SCCM Collection based on advertisement status or Status Messege ID’s

this post is just to make the application deployment easier.when you deploy any application to a collection,you might see faliure,no status and waiting.I had similar issues(number count was more).This collection gives you list of systems with faliure/no status/waiting status for particular advertisement.if you need more ,just customise it.

Collection:

select sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client from sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID  WHERE AdvertisementID = 'KBS20326' and (LastStateName = "Failed" or LastStateName='Waiting' or LastStateName='No status') and sys.Name like "US_%"

Where KBS20326 is advertisement ID ,just replace it with your ID.once you are ready with this collection,you can simply advertise it and get the better success rate,if you still have less success rate,get more into troubleshooting on the web 🙂

You can also create a collection based on the statusmessge IDS which can be obtained from sitesystem-->Advertisement status

Collection for Computers where particualr advertisement is not succeded with  status messgae ID's.

select SMS_R_SYSTEM.ResourceID not in (select SMS_ClientAdvertismentStatus.ResourceID from SMS_ClientAdvertisementStatus where SMS_ClientAdvertisementStatus.AdvertisementID = "KBS2035F" and SMS_ClientAdvertisementStatus.laststatusmessageID in (10009)")

Where 10009 is success state.The above collection will gives you list of computer which are not succeded.

With succeded state:

select sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client from sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID  WHERE AdvertisementID = ‘KBS2035F’ and LastStatusMessageID = 10009

Below are the list of SMS/SCCM status message ID's:

10050 Cache too small
10061 Download failed (retrying)
10003 Failed (bad environment)
0 No messages have been received
10054 Not enough space in cache
10045 Not enough space in cache
10034 Program cancelled by user
10022 Program completed successfully (reboot pending)
10009 Program completed with success
10008 Program completed with success
10004 Program failed
10007 Program failed
10006 Program failed
10057 Program failed (download failed – content mismatch)
10053 Program failed (download failed)
10058 Program failed (download/location failure)
10051 Program failed (no content)
10071 Program failed (retrying)
10072 Program failed (retrying)
10056 Program failed (retrying)
10070 Program failed (run time exceeded)
10021 Program failed (unexpected restart)
65535 Program received – no further status
10062 Program retrying (download/location failure)
10060 Program retrying (no content)
10005 Program started
10040 Program will not rerun
10055 Retry (Bad environment)
10037 Waiting for another program
10035 Waiting for content
10036 Waiting for user condition