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.

Prompted WQL Query - Create Query

Open the ConfigMgr console and browse to Monitoring | Overview |Queries. Next, click on the Create Query button.

Prompted WQL Query - Name

Provide a Name for your query and then click on Edit Query Statement…

Prompted WQL Query - Query Statement Properties

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.

Prompted WQL Query - Criteria Tab

This is where the fun begins. Click on the new “Star Button” (star) button.

Prompted WQL Query - Criterion Properties

From the Criterion Type drop-down box, select Prompted value.

Prompted WQL Query - 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.).

Prompted WQL Query - Criteria Tab - Prompted Value

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.

Prompted WQL Query - Query Language

This changes the window’s appearance as you can see in the above image.

Now, click on the “Play Button” play or execute button. This will run the query.

Prompted WQL Query - Input Query Value

At the Input Query Value window, enter what you are looking for and then click OK.

Prompted WQL Query - Query Results Preview

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

Prompted WQL Query - Queries

Again, there are no tricks to this process. Simply find your query in the console and run it.

Prompted WQL Query - Input Query Value - Operator

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.

Prompted WQL Query - Windows Server 2016 Computers

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.

Actionable dashboards? Yes, please with Right Click Tools Enterprise.

LAPS, BitLocker, and software update compliance all in an easy-to-view format with remediation just a right click away.

Back to Top