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