Show “Pending System Restart” Clients for Software Updates

Version: SCCM 2012 SP1 CU2

Update as of July 6, 2014: A different report using a more reliable approach has been created using Compliance Settings -> Monitor for Pending Reboot

These reports shows all SCCM clients running server or workstation operating systems and having atleast one software update deployment that indicates a status of “pending system restart”. This may be helpful in scenarios where servers are not automatically rebooted and you need a report to view which machines are in “pending system restart” status.

For server operating systems:

select  Distinct sys.AD_Site_Name0 as'AD Site',Sys.Resource_Domain_OR_Workgr0 as'Domain',  sys.Name0 as'Name', ws.LastHWScan, OPSYS.LastBootUpTime0 as'LastBootUpTime', OPSYS.Caption0 as'OS' from v_R_System sys
join v_AssignmentState_Combined assc on sys.ResourceID=assc.ResourceID
join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_GS_WORKSTATION_STATUS ws on sys.ResourceID=ws.ResourceID
join v_GS_OPERATING_SYSTEM OPSYS on sys.ResourceID=OPSYS.ResourceID
where sn.StateName like'%Pending system restart%'and sys.Operating_System_Name_and0 like'%server%'
ORDER by sys.AD_Site_Name0,Sys.Name0

For workstation operating systems:

select  Distinct sys.AD_Site_Name0 as'AD Site',Sys.Resource_Domain_OR_Workgr0 as'Domain',  sys.Name0 as'Name', ws.LastHWScan, OPSYS.LastBootUpTime0 as'LastBootUpTime', OPSYS.Caption0 as'OS' from v_R_System sys
join v_AssignmentState_Combined assc on sys.ResourceID=assc.ResourceID
join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_GS_WORKSTATION_STATUS ws on sys.ResourceID=ws.ResourceID
join v_GS_OPERATING_SYSTEM OPSYS on sys.ResourceID=OPSYS.ResourceID
where sn.StateName like'%Pending system restart%'and sys.Operating_System_Name_and0 like'%workstation%'
ORDER by sys.AD_Site_Name0,Sys.Name0

These reports will  display AD site, system domain,  system name, last hardware scan, last bootup time, and operating system caption.

Sample Output:

Show "Pending System Restart" Clients for Software Updates

Show “Pending System Restart” Clients for Software Updates