How to use SCCM Dynamic Queries in your Deployment Collections

In this post, we will be looking at using SCCM dynamic queries to populate collections in our deployments. As an 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 new software to be part of a baseline for workstations (for example), you will also add it to your task sequence.

In my past 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, we developed a fabulous list of operational collections that we can use for our day-to-day deployments. Now, with SCCM 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 of 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 exclude 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 fewer and fewer 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 of a package deployment using SCCM Dynamic Queries

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

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

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.

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.

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

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

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.

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 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 as we did above, combined with 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.

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.