Endpoint Insights
How to Create a Prompted WQL Query
Topics: Endpoint Insights
Did you know that you can write a prompted WQL query within the Microsoft Endpoint Manager Configuration Manager (MEMCM/ConfigMgr/MECM) console? Yes, you can! It is one of those nice features that almost no one knows about and there’s not much information found online about it. From time to time, within the forums, I’ll see a question about how to do this, so that’s what gave me the idea to write this blog post. Here you’ll find a step-by-step guide on how to create a prompted WQL query in the ConfigMgr console.
Why Would You Create a Prompted WQL Query?
That is a great question. In my opinion, the answer varies from one person to the next based on their needs. Sometimes the reasons are all centered around not having access to SQL Server and they want to query the database directly. Or, sometimes it is a matter of convenience. I sometimes hear, “I need to query ‘X’ all day. It would be helpful to have that setup for me, so I don’t have to do it each and every time.”
How to Create a Prompted WQL Query in ConfigMgr
The actual process for creating a prompted WQL query isn’t that different from the one for creating a normal query. The ONE KEY thing to remember is that you CAN’T use a prompted WQL query for a collection! It can only be used within the MECM console.
Open the ConfigMgr console and browse to Monitoring | Overview |Queries. Next, click on the Create Query button.
Provide a Name for your query and then click on Edit Query Statement…
Since writing this query is no different from a normal query, I will skip over the steps. But, at a high level, you need to select the displayed columns that you want to see. Next, click on the Criteria tab.
This is where the fun begins. Click on the new “
” (star) button.
From the Criterion Type drop-down box, select Prompted value.
Now, finish creating the query as you would any other query. Before clicking on the OK button, take a look at the next screenshot to make sure your results are similar to mine. In the next section, I will also show you how to test your query.
As a reminder, when you are at this stage the next high-level steps are to select the prompted attribute used for the prompted value. Then, select an Operator (is equal to, is less than, is like, etc.).
When you are finished adding the Prompted value criteria, it should look similar to the screenshot above. Now, click OK to create the query.
Testing Your Query
Before you hit the OK button, you can test your query to ensure that the results are what you expect. This is a fairly new feature to ConfigMgr, so if you don’t have it, it is time to upgrade. 😊
In order to test the query, first click on the Show Query Language button.
This changes the window’s appearance as you can see in the above image.
Now, click on the “
” play or execute button. This will run the query.
At the Input Query Value window, enter what you are looking for and then click OK.
Review the results and then click on the Close button. Yes, I know that the results look “messy” in the screenshot above, but the point is that there are results. By the way, these results are more or less what I expected to see, but I filed a frown ☹ because the OS caption and system name should be shown in the results window. Let’s hope it gets fixed sooner rather than later.
Query Example
The final query in my example, “List of Computers by OS,” (watch for word-wrapping) looks like this:
select
SMS_R_System.Name,
SMS_R_System.LastLogonUserName,
SMS_G_System_OPERATING_SYSTEM.Caption
from
SMS_R_System
inner join SMS_G_System_OPERATING_SYSTEM on
SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId
where
SMS_G_System_OPERATING_SYSTEM.Caption
like ##PRM:SMS_G_System_OPERATING_SYSTEM.Caption##
Using Your Query
Again, there are no tricks to this process. Simply find your query in the console and run it.
When you run the query, you will be prompted for a value.
Note: Make sure to include the percentage “%” sign(s) when using Is Like as your operator within the query.
There you go! I can see all of the Windows Server 2016 computers that I have within the MECM console. If you have any questions about how to create a prompted WQL query, please send me a tweet @GarthMJ.
