How to use SCCM Dynamic Queries in your Deployment Collections

Stephane FaubertSCCM5 Comments

In this post we will be looking at using SCCM dynamic queries to populate collections in our deployments. As a SCCM administrator, you most likely had to plan out mass deployments to all your servers or workstations or even both. How did you go ahead and populate your collections? Queries? Since the introduction of SCCM 2012, we now have a multitude of options, most notably:

  • Direct membership
  • Queries
  • Include a collection
  • Exclude a collection

Chances are, if you are deploying a new software to be part of a baseline for workstations (for example), you will also add it to your task sequence.

In my passed life, I must admit, I really did like queries. They can be such a powerful tool to populate your collections. I always was looking for ways to pimp the usual types of queries we use. For example, my fellow dude Benoit has given us a fabulous list of operational collections that we can use for our day to day deployments. Now, with SCCM 2012 we could create a deployment collection and simply include one of the operation collections and voilà.

But, that stays static. What I mean by that is if your collection targets 500 workstations, you will always target 500 workstations minus or more the workstations that get added as the query gets updated.

I personally like when things are a little more dynamic. If I target a deployment for 500 workstations, I would like to see that collection drop to 50, 40, 25 or whatever the count of objects as the deployment succeeds on workstations.

SCCM Dynamic Queries in layman’s terms

We have a deployment. Let’s use 7-Zip as an example. We want to deploy this on all our workstations. We would typically create a collection with a query along the lines of Select all objects where the operating system is like “workstation”. Simple right?

Now, let’s beef that up. What if we add to the same query another criteria that excludes all workstations where the Deployment ID for 7-Zip is successful? When we create the collection/deployment we will be targeting all workstations. As the workstations install the software and return a success code to their management point, this query will rerun itself and should yield less and less objects.

Caveat for your deployments

Now, you can use this for all your deployments. But to be optimal, you need to use Package deployments and not applications. Why? Simply because packages have the ability to have a custom schedule where you can repeat a schedule and set a rerun behavior whereas the Applications don’t have that possibility. So I will list all relevant information below for your queries, but aside from monitoring or reporting purposes, you won’t be able to rerun a deployment, unless you really delete and recreate a new deployment.

A real world example for a package deployment using SCCM Dynamic Queries

So I stated earlier, we start with a very basic package for 7-Zip.

SCCM dynamic queries

And as we typically do, this program is deployed to a collection, in this case I went very originally with Deploy 7-Zip.

SCCM dynamic queries

Nothing special with our collection the way we usually do it. Like I said, this is usually a collection along the lines of All Laptop or All Windows 7 & 10 and so on. My current query lists a grand total of 4 objects in my collection.

SCCM dynamic queries

Let’s right click this collection and go to the Membership Rules tab. You can clearly see the type of rule is set to Query. Let’s click on Edit to view the details of my query. Note: I set my updates on collections at 30 minutes. This is my personal lab. I would in no case set this for a real live production collection. Most aggressive I would typically go for would be 8 hours.

SCCM dynamic queries

[su_box title=”A typical query” style=”glass” title_color=”#F0F0F0″]System Resource.Operating System Name and Version is like %Workstation%[/su_box]

SCCM dynamic queries

Let’s press Show Query Language to go in text edit mode.

SCCM dynamic queries

As you can see above, I added:

[su_box title=”Append the following to your query” style=”glass” title_color=”#F0F0F0″] and SMS_R_SYSTEM.ResourceID not in (Select SMS_R_System.ResourceID from SMS_R_System inner join SMS_ClassicDeploymentAssetDetails on SMS_ClassicDeploymentAssetDetails.DeviceID = SMS_R_System.ResourceID where SMS_ClassicDeploymentAssetDetails.DeploymentID = “XYZ12345” and SMS_ClassicDeploymentAssetDetails.StatusType = “1”)[/su_box]

Replace XYZ12345 in the above query with your DeploymentID. Understanding WQL can be a challenge if you never played around with it. To understand it, let’s break it down in two parts.

  1. In between the parenthesis, I am asking SCCM to give me all objects (Resource ID) where the DeploymentID is XYZ12345 (our 7-Zip deployment) and the StatusType is 1 (success).
  2. Since we want to exclude these machines from the collection I simply negate the above query with a not statement. So give me all IDs that are not part of that sub-selection.

Press Ok. As you can see in the screenshot below, my count went down by two since I already had successfully deployed it to half my test machines.

SCCM dynamic queries

Pimp my package deployment

Ok, now that we have that dynamic query up and running, why not try and improve on the overall deployment technique, shall we? As you know, a program will be deployed when the Assignment schedule time is reached. If you have computers that are offline, they will receive their installation when they boot up their workstation, unless you have a maintenance window preventing it. But, let’s say you have a quite a few workstations you want to deploy,  you will more than likely have some failures here and there. Unless you have set a recurring schedule, it will not rerun. By having a dynamic collection like we did above, combined to a recurring schedule, you can reattempt the installation on all workstations that failed the installation without starting the process for nothing on a workstation that succeeded to install it.

SCCM dynamic queries

As I said earlier, the goal of this post is not necessarily to replace your deployment methods. It’s simply to give you an alternate method of deploying software on your objects and viewing in real time the success of the deployment. As the count goes down, you know which machines received it successfully and which didn’t.

Do you guys have any other methods to do this? If so, I would be curious to hear you guys out.

5 Comments on “How to use SCCM Dynamic Queries in your Deployment Collections”

  1. When trying to add the SMS_ClassicDeploymentAssetDetails components i’m getting an error for invalid query statement is this still valid

  2. I would never use advert status in a collection query. Make sure the program creates an entry in Add/Remove and query for Installed applications – yes that is an inventory item so you have to wait for inventory – but you could also force a inventory after installation. quick and painless via the command line. That all being said, use an application and it takes care of all of this for you.

  3. Hi Ryan!

    Thanks for your reply. The main reason I revert to using packages is simply for the ability to have a recurring schedule which you don’t have with an application.

    As for laptops, yes they will rerun the deployment. As would a desktop. But then, the behavior falls back to the installer. So if you are running a MSI, the workstation will skip the installation as it’s detected as installed on the laptop. An EXE would most likely rerun.

    However, don’t forget that the deployment won’t rerun on a workstation that has returned a success code since it won’t be in the collection anymore.

    Hope this clears it up !

  4. Why wouldn’t you just use applications? Aren’t you just re-creating the application detection method on the server side?

    Also, I’d be interested to see what this would do to laptops. If the computer has the policy to re-run every day, and is off the network for a week, will it rerun every day even if the package was successful?

Leave a Reply

Your email address will not be published. Required fields are marked *