Collection : For software distribution status if system pending for reboot

For software distribution status if system pending for 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 AS sms_r_system
     join SMS_StatMsg  as st on sms_r_system.Name = st.MachineName
     join SMS_AdvertisementStatusInformation sti on st.MessageID = sti.MessageID 
 where 
        sti.messagestate = 102
 

102 is the reboot pending code
you can get the complete list of codes using the following sql query

 select distinct messagestate,MessageStateName from dbo.v_AdvertisementStatusInformation