SCCM Collection Query All Clients where Advertisement not successful

Hi

I'm trying to create a collection with a dynamic membership query.
I want only the clients on which an advertisement status is not successful.

So I can see on which clients the advertisement didn't not apply.

When I use this query, I see the clients on which the advertisement was successful:

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 SMS_R_System.ResourceId in (select ResourceID from SMS_ClientAdvertisementStatus where AdvertisementID = "BMA20408" and LastStateName = "Succeeded")
So my idea was to set the != Succeeded
But then I have no clients in the collection.

Any Idea how I can solve that?
August 12th, 2010 3:13pm

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

 

Free Windows Admin Tool Kit Click here and download it now
August 19th, 2010 11:26pm

 

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

 

This will pull all the clients which are not sitting in success status, Make the collection refresh everyday to take out the successful clients from it.

 

  • Proposed as answer by sr1226 Wednesday, February 25, 2015 12:37 PM
  • Unproposed as answer by sr1226 Wednesday, February 25, 2015 12:37 PM
August 19th, 2010 11:26pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics