We can create a collection with the below dynamic query to list all clients where specific advertisement was not successful. All you need to change the advert ID and use it to your production environment to make management happy by reports.
SMS_R_SYSTEM.ResourceID not in (select SMS_ClientAdvertismentStatus.ResourceID from SMS_ClientAdvertisementStatus where SMS_ClientAdvertisementStatus.AdvertisementID = "ADV20408" and SMS_ClientAdvertisementStatus.laststatusmessageID in (10009))
Cheers!
hi - when pasting this into SCCM WQL I'm getting an error "The query statement that you entered is not valid. Please enter a valid query statement."
ReplyDeleteI've tried inserting a select/from statement before the above query but get the same error as well.
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
Yes, you are right.
ReplyDeletePlease try this-
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 = 'CEN207A3' and LastStateName = "Failed"
Hope it helps.