Long ago, I wrote a blog post on a report for finding the devices with pending reboot, more information is available on https://eskonr.com/2019/01/sccm-report-get-list-of-devices-with-pending-reboot-in-a-collection-with-different-states/
I was recently working on checking the compliance of the devices in SCCM for windows patching and I could see that the software update compliance is not that great due to various reasons.
Before I start to dig deeper into it, the first thing was to check the pending reboot of the devices.
In this blog post, I will provide 2 collections that will be useful for identifying the pending reboot devices.
The following WQL collection query can be used to create a collection for a list of all devices with a pending reboot.
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client from SMS_R_System join sms_combineddeviceresources on sms_combineddeviceresources.resourceid = sms_r_system.resourceid where sms_combineddeviceresources.clientstate != 0
Of all the devices pending reboot, I want to further drill down to see the pending reboot devices with no user currently logged on.
The following is the WQL collection query to list all devices with pending reboot and no user currently logged-on.
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client from SMS_R_System join sms_combineddeviceresources on sms_combineddeviceresources.resourceid = sms_r_system.resourceid where sms_combineddeviceresources.clientstate != 0
and sms_combineddeviceresources.CurrentLogonUser is NULL
You can further filter these queries to show online devices, last policy request is within 30 days etc.
Once you have the collection, you can either initiate the pending reboot from the collection using client notification (which inherits the client settings) or schedule toast notification for device restart.
The following is the sample WQL query which includes multiple sub-selected queries such as hardware inventory is older than 25 days and last policy request is within 25 days.
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System
where (SMS_R_System.ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId
where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) > 25) or SMS_R_System.ResourceId not in (select ResourceID from SMS_G_System_WORKSTATION_STATUS)) and
SMS_R_System.ResourceId not in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId
where(DATEDIFF(dd,SMS_G_System_CH_ClientSummary.LastPolicyRequest,GetDate()) < 25))
I hope you will find these queries useful in day to day operations.
