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

Leave a Reply

Your email address will not be published. Required fields are marked *