Show Servers Ordered by Last Boot Up Time

Version: SCCM 2012 R2

This query will show machines running server operating systems ordered by the last boot up time. In addition, this will flag systems that have not rebooted in over 12 months, 6 months, and 3 months. This query can be revised easily to show any number of thresholds using the datediff function and the case statement in SQL (see bold section).

Select Distinct Sys.Resource_Domain_OR_Workgr0 as'Domain', sys.Name0 as'Name', OPSYS.LastBootUpTime0 as'LastBootUpTime', OPSYS.Caption0 as'OS',
WHEN datediff(mm,OPSYS.LastBootUpTime0, getdate()) > 12 THEN 'Over 12 Months'
WHEN datediff(mm,OPSYS.LastBootUpTime0, getdate()) > 6 THEN 'Over 6 Months'
WHEN datediff(mm,OPSYS.LastBootUpTime0, getdate()) > 3 THEN 'Over 3 Months'
END as 'Status'
from v_R_System sys
join v_GS_OPERATING_SYSTEM OPSYS on sys.ResourceID=OPSYS.ResourceID
where sys.Operating_System_Name_and0 like'%server%'
ORDER by OPSYS.LastBootUpTime0, Sys.Name0