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?

• Moved by Stan White [MSFT]Microsoft employee, Moderator Tuesday, August 17, 2010 2:00 PM (From:Configuration Manager General)

Why not do "Failed"?

 http://myitforum.com/cs2/blogs/gramsey/archive/2006/10/20/How-To_3A00_-Create-a-Collection-Based-on-an-Advertisement-Status.aspx

 

 

I want to make the advertisement to this collection.
So when I have only Failed then the list would be empty.

Is there a list with all possible LastStateName ?

... where SMS_R_System.ResourceId not in (select ResourceID from SMS_ClientAdvertisementStatus where AdvertisementID = "BMA20408" and LastStateName = "Succeeded")

So my idea was to set the != Succeeded

Try using "not" instead of "!=". Haven't tested that with your example.

When I try use "not" I get an syntax error.

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

 

• Proposed as answer by Eric MattoonMicrosoft employee, Moderator Tuesday, August 24, 2010 3:17 PM
• Marked as answer by Eric MattoonMicrosoft employee, Moderator Thursday, August 26, 2010 4:02 PM

 

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

Hi,,sorry, but it looks like that I'm too stupid for copy and paste.

I tried this one:

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 not in (select SMS_ClientAdvertismentStatus.ResourceID from SMS_ClientAdvertisementStatus where SMS_ClientAdvertisementStatus.AdvertisementID = "P0120EFE" and SMS_ClientAdvertisementStatus.laststatusmessageID in (10021))

and always, whe I want ot save the script I got the message that there is a mistake. I'm not able to find the mistake - can please anyone help me?

Regards

Rolf