SCCM Collection query advertisement status

Hi,
I am using the following query for my Collections in SCCM to get the clients with an advertisement that was succesfull.
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 = ‘00120019' and LastStateName = “Succeeded”

This query is doing the trick just fine however I want to query more than one advertisement in this Collection. e.q.
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 = ‘00120019' and LastStateName = “Succeeded” AND AdvertisementID = ‘00120020' and LastStateName = “Succeeded” AND AdvertisementID = ‘00120021' and LastStateName = “Succeeded”.

This query does not return anything at all so I believe that this query is not ok. Can anyone tell/show me the right way to go?

Arjan

AHSBAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

merowingerCommented:
you have to choose an OR condition!
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 = '00120019' OR LastStateName = 'Succeeded' OR AdvertisementID = '00120020' OR LastStateName = 'Succeeded” OR AdvertisementID = '00120021' OR LastStateName = 'Succeeded'

Open in new window

AHSBAuthor Commented:
@merowinger
thx for the reply, however using 'OR' won't do the trick the way I want it to. I need every advertisement (in my case 4 advertisements) to be "Succeeded" before the client is added to the collection.
When I use 'OR'(just tested it) the client is added to the collection if only one advertisement is "Succeeded.

Arjan
merowingerCommented:
try this:


WHERE AdvertisementID = '00120019'
OR AdvertisementID = '00120020'
OR AdvertisementID = '00120021'
and LastStateName = 'Succeeded'
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

AHSBAuthor Commented:
@merowinger
Tried that too, however now I see the client wether or not the advertisement is succeeded, failed or there at all. If only one advertisement is found the client is displayed.

The query need to be build that only when all four advertisements have status "Succeeded" the client is added to the collection. Above queries using OR won't filter enough to get to this point.

however....thx for helping.
merowingerCommented:
try to use ( ) characters

WHERE (AdvertisementID = '00120019'
OR AdvertisementID = '00120020'
OR AdvertisementID = '00120021')
and LastStateName = 'Succeeded'
AHSBAuthor Commented:
I tried the suggestied solution and had no luck. This is wat I have already tried:
WHERE (AdvertisementID = '00120019'
OR AdvertisementID = '00120020'
OR AdvertisementID = '00120021')
and LastStateName = 'Succeeded'

This one showed the client in the collection

WHERE (AdvertisementID = '00120019'
OR AdvertisementID = '00120020'
OR AdvertisementID = '00120021')
and LastStateName = 'Failed'

This one does not show the client in the collections since all advertisement have succeeded

WHERE (AdvertisementID = '00120019'
AND AdvertisementID = '00120020'
ANd AdvertisementID = '00120021')
and LastStateName = 'Succeeded'

This one did not show the client in the collection although all advertisements have succeeded.

WHERE (AdvertisementID = '00120019'
OR AdvertisementID = '00120020'
OR AdvertisementID = '00120021'
OR AdvertisementID = '00120099')  <-- this one was never assigned!!!!
and LastStateName = 'Succeeded'

This one showed the client in the collection although one of the assignements was never assigned to that client. Hence the 'OR' statement tries until one of the advertisments is found and succeeded. This is not the solution I need.

I also tried:
using two select statements and adding them together using UNION ALL.
e.g.
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

SCCM does not accept the UNION ALL method.

I tried using () characters in different ways, however these do not seem to do their magic.

I tried adding multiple queries to the collection, however this is almost the same as using OR. If only one of the queries was true, the client showed up.

I cannot seem to get the query right here! Is it at all possible?

Arjan
 
ExemplarCommented:
Have you tried to create a separate query targeting the second AdvertisementID?  In other words, having two separate queries for the collection.
AHSBAuthor Commented:
@Exemplar
 Yes I tried that. On the Collection I have added two seperate queries. This results in showing all clients that fullfill the first query and showing all clients that fullfill the second query. What I need is to show the clients that only fullfill the first AND second query. It seems that SCCM cannot handle these kinds of queries.

AHSBAuthor Commented:
All credits go to my collegae for the next solution......

This query gives me the result I need. It shows only the clients that had the advertisements and were succeeded.

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 = "00120126" and LastStateName="Succeeded") and SMS_R_System.ResourceId in (select ResourceID from SMS_ClientAdvertisementStatus where AdvertisementID = "00120137" and LastStateName="Succeeded") and SMS_R_System.ResourceId in (select ResourceID from SMS_ClientAdvertisementStatus where AdvertisementID = "00120118" and LastStateName="Succeeded") and SMS_R_System.ResourceId in (select ResourceID from SMS_ClientAdvertisementStatus where AdvertisementID = "0012012E" and LastStateName="Succeeded") and SMS_R_System.ResourceId in (select ResourceID from SMS_ClientAdvertisementStatus where AdvertisementID = "00120138" and LastStateName="Succeeded")

So this question has been answered.

Arjan

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael umoetukCommented:
This definitely will not work in 2012 R2. As far as I know, there is no AdvertisementID in SCCM 2012 R2. I stand to be corrected.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.