February 7 2011

SCCM report for all server details including age

I recently had a requirement to report on server ages for the machines in our environment. Adding the ‘BIOS Manufacture date’ and ‘OS Install Date’ should be a good indication of this. The query will also return some other helpful fields like model, manufacturer, RAM and processor:

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

 
 



----------------------------------------------------------------------------
I use a maximum of one Google Ad per post to help offset some of my blog hosting costs.

----------------------------------------------------------------------------


Tags: , , , ,

Posted February 7, 2011 by danovich in category "SMS / SCCM

Leave a Reply