Collections based on software updates deployment status in Configuration Manager

ConfigMgr sccm patching status based collections

LastEnforcementMessageID
LastEnforcementMessageName

1        Enforcement started   

3        Waiting for another installation to complete

6    General failure

8    Installing update   

9    Pending system restart   

10  Successfully installed update   

11  Failed to install update   

12  Downloading update   

13  Downloaded update   

So in this example we would like to use the status of reboot pending, the WQL query for the collection should look like this:

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 AS sms_r_system    
    inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid    
where    
    c.LastEnforcementMessageID = 9