Endpoint Insights
Combining Two WQL Queries in One Collection
Topics: Endpoint Insights
In a recent forum post someone asked for help with combining two WQL queries (see below WQL Query 1 and WQL Query 2) into one query. It is possible to combine two WQL queries into one, but it all depends on what both queries are looking for. In the simplest terms, WQL is NOT SQL and not everything you can do in SQL will work with WQL. It takes time, patience, and practice in order to know what queries to combine and to confirm that the results from a WQL query are correct. This blog post will talk about your options when it comes to combining two WQL queries.
In this example WQL Query 1 involves creating a collection for all computers without the ConfigMgr client installed, and WQL Query 2 involves creating a collection for all computers that haven’t had contact with the ConfigMgr server in 90 days.
Let’s get starting by looking at the two queries.
WQL Query 1
This first query is looking for all computers that were discovered by ConfigMgr using any of the discovery methods, but these computers do not have the ConfigMgr client installed.
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
WHERE
sms_r_system.client IS NULL
WQL Query 2
This second query is looking for all computers that have the ConfigMgr client installed BUT have not reported hardware inventory to ConfigMgr in 90 days.
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
WHERE
sms_r_system.resourceid IN (
SELECT
sms_g_system_workstation_status.resourceid
FROM
sms_g_system_workstation_status
WHERE
Datediff(dd, sms_g_system_workstation_status.lasthardwarescan, Getdate()) > 90)
How can you combine these queries into one query to find both results, so that ultimately you can create a collection for the combined query?
There are two ways you can answer this question. The obvious answer, Option #1, is to combine the queries, so that the results show you both results within one WQL query. The second answer, which isn’t so obvious, Option #2, is NOT to combine the queries. Instead you can have two separate queries in a collection. The end result will be a combination of both in one collection.
Let’s look at the two options below.
Combined Query
In the combined query, the results will show all computers that were discovered by ConfigMgr using any of the discovery methods, but don’t have the ConfigMgr client installed. It will also show all computers that have the ConfigMgr client installed BUT have not reported hardware inventory to ConfigMgr in 90 days.
The query below does that.
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
WHERE
sms_r_system.client IS NULL
or sms_r_system.resourceid IN (
SELECT
sms_g_system_workstation_status.resourceid
FROM
sms_g_system_workstation_status
WHERE
Datediff(dd, sms_g_system_workstation_status.lasthardwarescan, Getdate()) > 90)
When I created the collection, using the Create Device Collection Wizard, see how there is only one query in the screenshot below?
Two Queries for a Collection
Many people seem to think that you can only have one query per collection. That’s not true! You can have more than one query per collection.
How does it work? Honestly there are no tricks here. Simply create the collection with the first query and then add the second query. You’re done!
Below is what the collection membership rules look like in the Create Device Collection Wizard.
Notice that I added both queries to the Membership rules.
Now I’ll show you that the results are exactly the same. Below is a screenshot from my console. As you can see from the arrows, both collections produced the same number of computers, 47.
I hope that you have found this information useful and if you have any questions, please feel free to contact me @GarthMJ.
Do you have an idea for a blog post about a ConfigMgr query or reporting topic? Let me know. Your idea might become the focus of my next blog post!
