Share This Post

Using SQL to Search Collection Queries

Computers aren’t perfect. Sometimes they just don’t report properly to SCCM. In one particular case, we had a computer whose Add/Remove Programs information was not populating in the SCCM database. For this reason, they were showing applicable for several recurring advertisements we had out there and kept having jobs forced on them. Not fun for the user.

It was requested of me to manually filter the computer out of the query based collections to give them time to rebuild the machine. Even though I was hesitant to do so I finally did, adding something like AND Name != ‘COMPUTERNAME’ at the end of the query statements.

A month has past and now I’ve been asked to remove those statements, as the computer is healthy again. Easy enough…oh wait…what collections did I modify again?

I went to my old friend – the SQL database – to see if it could help me with my problem. Turns out, it had everything I needed.

I logged on to my server and opened SQL Server Management Studio. After connecting, I clicked “New Query” and made sure the SCCM database was selected in the “Available Databases” dropdown. I then used the following statement:

SELECT *
FROM v_CollectionRuleQuery crq JOIN v_Collection col on crq.CollectionID=col.CollectionID
WHERE crq.QueryExpression LIKE ‘%COMPUTERNAME%’

This gave me everything I needed in order to find the queries that contained the computer in question. SQL to the rescue yet again!

Share This Post

Leave a Reply