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]