Configuration Manager Report Query for Server Name and Properties

If you want a report on the hardware and OS properties of the Windows servers in your Configuration Manager environment — manufacturer, model, RAM, processor, BIOS date, OS install date — this query pulls it all together from standard hardware inventory tables, ready to run directly or drop into an SSRS report.

The query

SELECT distinct
CS.name0 AS 'Server Name',
OS.Caption0 AS 'OS',
CU.Manufacturer0 AS 'Manufacturer',
CU.Model0 AS 'Model',
RAM.TotalPhysicalMemory0/1024 AS [RAM (MB)],
Processor.Name0 AS 'Processor',
BIOS.ReleaseDate0 AS 'BIOS Manufacture Date',
OS.InstallDate0 AS 'OS Install Date'
FROM
v_R_System CS
FULL JOIN v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
FULL JOIN v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
FULL JOIN V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
FULL JOIN v_GS_PROCESSOR Processor on Processor.ResourceID=CS.ResourceID
FULL JOIN v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
FULL JOIN v_GS_COMPUTER_SYSTEM CU on CU.ResourceID = CS.ResourceID
WHERE CS.Operating_System_Name_and0 LIKE '%nt%server%' and CS.Client0 = 1
GROUP BY
CS.Name0,
OS.Caption0,
CU.Manufacturer0,
CU.Model0,
RAM.TotalPhysicalMemory0,
BIOS.ReleaseDate0,
OS.InstallDate0,
Processor.Name0,
BIOS.ReleaseDate0
ORDER BY CS.Name0

The WHERE CS.Operating_System_Name_and0 LIKE '%nt%server%' filter restricts results to server operating systems only, and CS.Client0 = 1 limits it to machines with an active ConfigMgr client. Both of these are worth double-checking against your own OS naming if you have unusual or renamed OS caption strings in your environment.

Add or remove columns from the SELECT and matching GROUP BY list to tailor the report — just remember any column you add to SELECT needs to be added to GROUP BY too, since this query doesn’t use aggregate functions on those fields.

Resources

• query
• How to create a Configuration Manager Report using SSRS
• WQL
• our SQL & WQL query library