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]
%d bloggers like this: