This is a query to gather som basic information from the clients that might be useful in different scenarios. You may edit|add|delete information as you please. If you don’t know the exact name of the field you wish to add to can open the SQL Server Management Studio and start a new query. Type in SELECT * FROM SMS_<sitecode>.DBO.[v_R_System| v_GS_PC_BIOS|v_GS_OPERATING_SYSTEM| v_GS_X86_PC_MEMORY| v_GS_PROCESSOR| v_GS_SYSTEM_ENCLOSURE] Then you’ll get all the fields from the views used in this query. You can of course add other views|tables as well, but then you’ll have to find a common denominator in order to connect and filter the information correctly.

This report query gathers information from Windows 7 clients that has the SCCM client installed.

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.SMBiosBiosVersion0 as ‘BIOS Version’,
OS.InstallDate0 as ‘OS Install Date’

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%workstation 6.1%’ and CS.Client0 = 1
group by
Order by CS.Name0

Tagged with:

Leave a Reply