SCCM: How to create a collection for computers without a specific software installed.

As part of a Windows 10 migration, there is a requirement to push our users to begin using Microsoft OneDrive for Business.
However, how is a user expected to begin using a new application without first having that application installed?

With this in mind, it wasn’t long before i came to the realization that there is an unknown number of computers within the organisation that do not have this specific piece of software installed.

And so naturally, as a requirement, it was quickly clear that this needed to be rectified.

However, before i was able to begin, we needed to know our targets – so how do I identify the computers that are missing Microsoft OneDrive?

I have not had to do something like this before. I have previously worked with some reporting with SCCM.

Nevertheless, I didn’t feel like I needed to start building SQL Queries for this and opted for a different approach in trying to achieve my goals.

So what was the idea?

I’ll create a collection that contains a query, that adds all systems that have Microsoft OneDrive for business installed.

Then, I’ll create a collection for all systems, that Excludes the collection we created above.

This should then in return leave me with a collection of all computers without Microsoft OneDrive.

How did i accomplish this?

Part 1: Create a collection to find computers with OneDrive Installed

1. First, create a new collection
2. Enter the name, choose the limiting collection, then press next.
3. On the Membership Rules page, click on “Add Rule” and select “Query Rule“
4. Enter a name for the query rule, then click on “Edit Query Statement“
5. This will open a new window, click on “Show Query Language“

For the query rule, i used the WQL Query below. If you want to make changes to this query and use it for a different application or specify a version, then refer to the screenshot beneath the query which shows you the text that you will need to change.

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  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 like "Microsoft%OneDrive%"  and  SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "%" order by SMS_R_System.Name

6. Click OK a few times and complete creating the collection.
7. Once the collection has completed updating with the assets. We can see the total number of computers that have OneDrive installed!

Part 2: Create a collection – that Excludes the collection created in Part 1.

Now that we have a collection of all computers that have OneDrive installed. We want to create a collection of all computers that Don’t have OneDrive installed.
To do this, we’re going to create an exclude query.

1. First, create a new collection
2. Enter the name, choose the limiting collection, then press next.
3. On the Membership Rules page, click on “Add Rule” and select “Exclude Collection“
4. Find the collection that you created in step 1 and choose to exclude it.
5. Click OK on the following pages and complete creation of the collection.

And that is it! We now have a collection that shows us all the computers that do not have onedrive installed.

If you want to see it working – install OneDrive on a computer that doesn’t have it installed and you should see the total count on first collection that we created increase by 1, and on the second collection decrease by 1.

I hope that this post has been helpful.

Thanks for reading.