Nov 062014
 

As I learn more about writing SQL queries, I have discovered that revisiting a utility script such as one that I have modified previously, and then adding to or rewriting it again, to be a very fun exercise.

In this incarnation of my PC Fleet Vitals query script. I have added both a Chassis type and a very rough video card lookup. There’s probably a better way to look up video card’s but the table I build in this query works for now. It isn’t showing some built in cards unfortunately, but it can catch discreet video cards like nvidia. It would be worth it to modify that little bit to your own environment, and perhaps contribute snippits back to me so I can add to the table. 🙂

The table for chassis type is lifted from Jon Marcum’s answer to a chassis lookup table question in this technet forum thread. I can’t claim that little snippit of query for my own, but it is quite useful for translating chassis type codes over to human readable form.

Enjoy, and please by all means let me know if you find this useful or if there’s something you think I should add.

-- PC Profile query for CM12
-- By Robert Hollingshead (roberthollingshead.net)
-- 
-- Profile of all PC's in your environment with Processor, Hard Drive, Chassis type, Memory, etc. 
-- Now includes video card, but this is experimental and may need tweaking by you. 


select distinct

CS.Manufacturer0 [Make],
CS.Model0 [Model],
CH.Chassis [Chassis],
CS.Name0 [Hostname], 
-- Replace {your domain here} below with YOURDOMAIN\
replace(CS.UserName0,'{your domain here}','') [Primary User],
REPLACE(SUBSTRING(USR.manager0,4,CHARINDEX(',OU',USR.manager0,3)-4),'\,',',') [Manager],
OS.Caption0 [OS],
OS.InstallDate0 [Image Date],
cast(CPU.MaxClockSpeed0/1000.00 as decimal(10,2)) [Speed (GHz)],
CPU.Name0 [CPU],
CPU.NumberOfCores0 [Cores],
CPU.IsHyperthreadCapable0 [Hyperthread],
DSK.Caption0 [HDD],
cast(DSK.Size0/1000.00 as decimal(10,2)) [HDD Capacity (GB)],
cast(LDSK.Size0/1000.00 as decimal(10,2)) [C Size (GB)],
cast(LDSK.FreeSpace0/1000.00 as decimal(10,2)) [C Free (GB)],
cast((LDSK.FreeSpace0 * 100.00)/(LDSK.Size0 * 1.00) as decimal(10,2)) [C %Free],
cast(OS.TotalVirtualMemorySize0/1000.000 as decimal(10,3)) [Virtual Memory (GB)],
cast(OS.TotalVisibleMemorySize0/1000.000 as decimal(10,3)) [Visible Memory (GB)],
vid.Description0 as [Display Controller]

from v_GS_COMPUTER_SYSTEM CS

left join v_GS_PROCESSOR CPU on CS.ResourceID = CPU.ResourceID
left join v_GS_DISK DSK on CS.ResourceID = DSK.ResourceID
left join v_GS_OPERATING_SYSTEM OS on CS.ResourceID = OS.ResourceID
left join v_GS_SYSTEM SYS on CS.ResourceID = SYS.ResourceID
left join v_GS_LOGICAL_DISK LDSK on CS.ResourceID = LDSK.ResourceID
left join v_R_User USR on CS.UserName0=USR.Unique_User_Name0

-- This creates a table of display drivers. It is very customized so it may need adjustments later.
left join (
select distinct * from dbo.v_GS_PNP_DEVICE_DRIVER PNPDD

where (Manufacturer0 like 'nvidia' and Name0 not like '%audio%')
		or (PNPDD.Name0 like '%graphics%' and PNPDD.PNPDeviceID0 not like 'ROOT\LEGACY%')
		or (PNPDD.Name0 like '%vga%' and DeviceID0 not like 'ROOT\LEGACY_VGASAVE%' and PNPDD.Name0 not like '%usb%'))
	  VID on CS.ResourceID=VID.ResourceID

-- This creates a table of chassis types.
-- Based on John Marcum's answer to a chassis lookup table in the following forum:
-- https://social.technet.microsoft.com/Forums/systemcenter/en-US/7abf9a9a-a160-4462-9676-3028957944b4/creating-a-chassis-type-lookup-table-in-sccm-db?forum=configmgrreporting
left join (select 

			CS.Name0,
			Chassis = CASE SE.ChassisTypes0
			WHEN '1' THEN 'Other' WHEN '2' THEN 'Unkown' 
			WHEN '3' THEN 'Desktop' WHEN '4' THEN 'LowProfileDesktop'
			WHEN '5' THEN 'Pizza Box' WHEN '6' THEN 'Mini-Tower' 
			WHEN '7' THEN 'Tower' WHEN '8' THEN 'Portable' 
			WHEN '9' THEN 'Laptop' WHEN '10' THEN 'Notebook' 
			WHEN '11' THEN 'Hand - Held' WHEN '12' THEN 'Docking Station' 
			WHEN '13' THEN 'ALL- IN -One' WHEN '14' THEN 'Sub Notebook' 
			WHEN '15' THEN 'Space Saving Chassis' WHEN '16' THEN 'Lunch Box' 
			WHEN '17' THEN 'Main System  Chassis' WHEN '18' THEN 'Expansion Chassis' 
			WHEN '19' THEN 'Sub Chassis' WHEN '20' THEN 'Bus Expansion Chassis' 
			WHEN '21' THEN 'Peripheral Chassis' WHEN '22' THEN 'Storage Chassis' 
			WHEN '23' THEN 'Rack Mounted Chassis' WHEN '24' THEN 'Sealed  CASE PC' 
			WHEN '25' THEN 'Tablet -PC'
			END

			from v_GS_SYSTEM_ENCLOSURE SE

			left join v_GS_COMPUTER_SYSTEM CS on SE.ResourceID=CS.ResourceID
		  ) CH on CS.Name0=CH.Name0

where 
SYS.SystemRole0 like 'Workstation'  and
LDSK.Caption0 like 'C:' and
DSK.DeviceID0 like '\\.\PHYSICALDRIVE0'


-- order by Make, Model, Hostname
order by [Make],[Model],[Hostname]
Jul 182014
 

A while back I wrote a MS SQL query to gather vitals for a PC Fleet in SCCM 2012. I have now updated it to include some more information like Make and information for the system partition (size/free/% used). I’ve also changed some of the columns to use the more Appealing Giga units of measurement instead of Mega.

This is so that if anyone ever asks what you have out there,  you can run a quick query and say “HERE YOU GO!” Enjoy! Written in Microsoft SQL Server Management Studio 2012.

NOTE: Search and replace {domain\} with your domain name or the query will fail, just FYI. Also, when you copy and paste, the lines will remain intact.

select distinct

CS.Manufacturer0 [Make],
CS.Model0 [Model],
CS.Name0 [Hostname], 
replace(CS.UserName0,'{domain\}','') [Primary User],
OS.Caption0 [OS],
OS.InstallDate0 [Image Date],
cast(CPU.MaxClockSpeed0/1000.00 as decimal(10,2)) [Speed (MHz)],
CPU.Name0 [CPU],
CPU.NumberOfCores0 [Cores],
CPU.IsHyperthreadCapable0 [Hyperthread],
DSK.Caption0 [HDD],
cast(DSK.Size0/1000.00 as decimal(10,2)) [HDD Capacity (GB)],
cast(LDSK.Size0/1000.00 as decimal(10,2)) [C Size (GB)],
cast(LDSK.FreeSpace0/1000.00 as decimal(10,2)) [C Free (GB)],
cast((LDSK.FreeSpace0 * 100.00)/(LDSK.Size0 * 1.00) as decimal(10,2)) [C %Free],
cast(OS.TotalVirtualMemorySize0/1000.000 as decimal(10,3)) [Virtual Memory (GB)],
cast(OS.TotalVisibleMemorySize0/1000.000 as decimal(10,3)) [Visible Memory (GB)]

from v_GS_COMPUTER_SYSTEM CS

left join v_GS_PROCESSOR CPU on CS.ResourceID = CPU.ResourceID
left join v_GS_DISK DSK on CS.ResourceID = DSK.ResourceID
left join v_GS_OPERATING_SYSTEM OS on CS.ResourceID = OS.ResourceID
left join v_GS_SYSTEM SYS on CS.ResourceID = SYS.ResourceID
left join v_GS_LOGICAL_DISK LDSK on CS.ResourceID = LDSK.ResourceID

where 
SYS.SystemRole0 like 'Workstation'  and
LDSK.Caption0 like 'C:' and
DSK.DeviceID0 like '\\.\PHYSICALDRIVE0' 

order by Make, Model

 

Feb 262014
 

Here’s a nifty little query that will return the physical memory configuration of each computer registered in SCCM 2012. I’m excluding the System ROM (where layeth the BIOS) because we don’t really care about that here. We want the RAM! Written in SQL Server Management Studio 2012.

select 

v_GS_PHYSICAL_MEMORY.DeviceLocator0 as [Slot],
v_GS_PHYSICAL_MEMORY.Capacity0 as [Size],
v_GS_PHYSICAL_MEMORY.Manufacturer0 as [Manufacturer],
v_GS_PHYSICAL_MEMORY.PartNumber0 as [Part Number],
v_GS_Computer_System.Model0 as [Model],
v_GS_Computer_System.Name0 as [Hostname],
v_GS_Computer_System.UserName0 as [Username]

from v_GS_PHYSICAL_MEMORY

left join v_GS_COMPUTER_SYSTEM on v_GS_PHYSICAL_MEMORY.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID

/* We don't care about the system rom. */
where v_GS_PHYSICAL_MEMORY.DeviceLocator0 not like 'SYSTEM ROM'

/* Sort by hostname. */
order by v_GS_Computer_System.Name0
Feb 242014
 

Here is a SQL Query to generate a list of each Computer System in the SCCM 2012 Database, and then list CPU/Memory/Disk information. I wrote this to display Physical Disk 0. There are two different where clauses. The first one, commented out, can be used to pull up specific computers based upon the conditions you set. The second one just uses Physical Disk 0 as a condition. Written using SQL Server Management Studio 2012.

select distinct

v_GS_COMPUTER_SYSTEM.Model0 as [Model],
v_GS_COMPUTER_SYSTEM.Name0 as [Hostname], 
v_GS_COMPUTER_SYSTEM.UserName0 as [Primary User],
v_GS_PROCESSOR.MaxClockSpeed0 as [Speed],
v_GS_PROCESSOR.Name0 as [CPU],
v_GS_PROCESSOR.NumberOfCores0 as [Cores],
v_GS_PROCESSOR.IsHyperthreadCapable0 as [Hyperthread],
v_GS_DISK.Caption0 as [HDD],
v_GS_DISK.Size0 as [HDD Capacity],
v_GS_OPERATING_SYSTEM.TotalVirtualMemorySize0 as [Virtual Memory],
v_GS_OPERATING_SYSTEM.TotalVisibleMemorySize0 as [Visible Memory]

from v_GS_COMPUTER_SYSTEM

left join v_GS_PROCESSOR on v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PROCESSOR.ResourceID
left join v_GS_DISK on v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_DISK.ResourceID
left join v_GS_OPERATING_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID

/* where (v_GS_COMPUTER_SYSTEM.Name0 like '{yourstringhere' ) and
v_GS_DISK.DeviceID0 like '\\.\PHYSICALDRIVE0' */

where v_GS_DISK.DeviceID like '\\.\PHYSICALDRIVE0'

order by [Visible Memory]