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