SCCM 2012 Reporting for dummies: How to construct and use queries

In this tutorial, we will discuss Queries on the monitoring tab. The Queries in SCCM 2012 are constructed in WQL (WMI Query Language). If you’ve had any experience in SQL then WQL should be very familiar to you.

First, click on the Queries node in the Monitoring tab and you should see all the pre-defined queries available to you.

queries console sccm 2012

The rule I live by is do not modify the default queries. Always create new queries. The same goes for collections. For now, we’ll run one of the default queries to see what it looks like. Click on the “All Client Systems” query and this will pull up all the systems that have a SCCM client on it.

all clients system queries show

Now that you’ve seen what a pre-defined query looks like, we’ll create our own query. But first, we’ll run through editing a pre-defined query (All Client Systems) to explain the process.

Right click “All Client Systems” and select Properties.

From the properties page, you can select the Object Type that the query will run against and choose whether to limit the query against a specific collection using the Collection Limiting grouping. Click on Edit Query Statement. This is where you define how the query runs and this is where it starts to get a little bit more complex.

The General tab shows all the data that the query will pull up and organize against. It will show what columns will appear and whether they are sorted in your results pane after running the query. It is similar to the SELECT clause in SQL.

The Criteria tab shows the rules on what the data will filter against. The more “criteria” you specify, the more specific the machines that are returned. It is similar to the WHERE clause in SQL.

The Joins tab specify the tables that the query will run against. It is similar to JOIN clause in SQL.

If you’re quite good with WQL or have a query you have pulled off the net, you can click Show Query Language and put your query there rather than use the tabs to build your query.

Now that you understand the fundamentals of a SCCM WQL query, we’ll go into creating a simple query to run.

Right click on the Queries node in the Monitoring tab and select Create Query.

This should open up the Create Query Wizard. It should look exactly the same as the query properties screen so you should be familiar with this. We’re going to be creating a query that pulls up systems that are using “Windows 7” OS so name the query “All Windows 7 Machines”. We’ll leave all the settings as they are and we’ll click on Edit Query Statement.

This screen will look very familiar because it is the same screen the query properties we looked at earlier.

Click on Criteria tab and click on the star icon to create a new criteria. We’ll leave the Criterion type as a Simple Value but we do need to specify where we want to run this query against so click Select.

Click the Attribute class drop down box and select Operating System from the list of attributes classes. We’ll then select Caption from the list of Attributes from the Attributes drop down box.

We’ll then click OK and on the Criterion Properties screen, we’ll change the Operator to “is like” and fill out the value field to “%Windows 7%”. We can choose the Value button and specify the value we want but since we want to pick up all Windows 7 editions, we’re choosing the LIKE operator and “%Windows 7%” as the value. The percentage signs are wildcard characters which mean anything before and after “Windows 7” in this context.

Click OK. You can click “Show Query Language” if you want to see what the WQL query looks like and you can click OK again to complete.

Once you are happy, click Next on the Create Query Wizard and review the settings. Click Next again when you are happy and voila! You have created your first query.

Now right click the new query you have created and select Run. You’ll now notice you’ll get a “Results for all Windows 7 Machines” sub-node appear under the Queries node. Any query you run will create new sub-nodes for easy of navigation.

The second thing you’ll notice is that the results pane shows a lot of information but not all very useful! This is where we need to go back and edit the query to restrict what types of information is shown. Remember the General tab earlier in this tutorial? That’s where we’re heading so click back on to the Queries node and Right click your new “All Windows 7 Machines” query and click Properties. In the Properties page, click Edit Query Statement…

From this tab, you can add whatever column you like to view whenever you run this query. For now, we’ll choose (all from System Resource) Name, Client, IP Addresses, Obsolete and Configuration Manager Assigned Sites.

After you’ve completed this, click OK out of the forms and click back on your “Results for All Windows 7 Machines”. You’ll now notice that the result pane shows more relevant results! Congrats, you’re now good to go with Queries!  You can use these queries to then either view data inside your SCCM 2012 database, create query based collections or any other good stuff.

email

Written by , Posted .
  • mikeymouth2000 .

    Awesome, really helpful…do you by chance know what the attribute value is for device affinity. I’m trying to pull a report together that lists users/systems affinity and can’t seem to find the right one to select. The Machine assigned to user just says yes/no not actual values. Suggestions???