SCCM Collection Query for devices with specific software installed and version or below

Last updated on September 27th, 2021 at 01:02 am

()

If you need to identify machines with specific software installed on a device but would also like to know whether they have a certain version of the stated software or below, then you can use the below query.

This can help with software upgrades to identify machines that have not yet been upgraded.

SCCM Collection Query

select distinct
SMS_R_System.Name,
SMS_R_System.ClientVersion
from SMS_R_System
inner join SMS_G_System_ADD_REMOVE_PROGRAMS
on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Shoretel Communicator"
and SMS_G_System_ADD_REMOVE_PROGRAMS.Version <= "17.60.9211.0"
order by SMS_R_System.Name, SMS_R_System.ClientVersion

You can change the where statements for the software you want to query and also the version number. In the example above, we are using Shoretel Communicator and version 17.60.9211.0, which actually translates as – return to me any machines that have Shoretel Communicator v12.3 (17.60.9211.0) or below install on them.

You can also change the = for LIKE in the query if you want to use the % wildcard for the DisplayName value.

How do I create the SSRS report?

We have now created a guide that will help you to create your SSRS reports based on the data that this guide will pull into the Configuration Manager database.

You can access this guide by clicking the link below:

Create an SSRS Report using Configuration Manager Database Data

Comments

If you have any problems with any of the above, then please leave your comments and questions below using our comments system and we will try to get back to you as soon as we can.

We also like to hear back any success stories too, so if you have used our guide and found it helpful, we would love to hear from you.

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Free Subscription

If you want to be notified when we post more quality guides like this one, sign up to our free subscription service and you will receive an email when a new post is live.

Join 450 other subscribers.

No need to worry, we will not be filling your inbox with spam and you can unsubscribe anytime you like.

4 Comments

  1. Hi thanks for the query but not giving the results

  2. Hi thanks for the query but not giving the results

  3. Hi thanks for the query but not giving the results

Leave us a message...

This site uses Akismet to reduce spam. Learn how your comment data is processed.