SCCM Queries

This post is just a reminder on how to create Queries in SCCM.  AKA Queries for Dummies
Queries can be used to query the client inventory database and return results which match query parameters.
Status Message Queries, as the name suggests, query the status message logs to retrieve the occurrence of events with the SCCM system.

Often admins will query the database directly.  The Queries and Status Message Queries areas provide GUI interfaces for new queries and can show the generated Windows Management Instrumentation Query Language (WQL) code that is generated.

This item is about how to use the GUI interfaces to create a functioning query.

Queries

Right click under Monitoring/Overview/Queries and click Create Query

So far with the few queries I've created I have not needed to change the Object Type from System Resource.

Set a limiting collection if you have a particular set of objects in a collection to query

Enter a Name and Comments and click Edit Query Statement

Click Edit Query Statement and wait for the GUI interface to load.

Click the button with a yellow sun icon to add a class.

Click Select and them choose the Class and Attribute you are interested in.  For example try System Resource as the class and Name as the attribute

Do the same again but this time choose the Last Logon User Name attribute

Now you have two pieces of information that will be obtained.

Next you set the Criteria - click the Criteria tab

Click the yellow sun button to add a Criteria

In Criteria Properties select how the value will be set in the Criterion Type.  Will be fixed or will you get the query to prompt for a value?  For this example try prompt

Next Click System and select the Attribute Class as System Resource and Attribute as Name.

Back on the Criterion Properties set the operator.  For this example choose "is equal to"

Prompt will be set to <prompted value>

Click the OKs and run the query.  Enter a machine name and the query will return the last user logged on.

Sharp eyes among you may realise that this is similar (but the other way round) to one of the built in queries "Systems by Last Logged on User"

Now you've got a default query and a shiny new one all of your own!  From here you should be able to experiment and obtain the data you need.

Status Message Queries (SMQ)

Find these under Monitoring\System Status\Status Message Queries.  There are numerous built in ones but we want our own nice shiny ones !

Half the trick here is identifying what kind  of SMQ you are looking for.  One possible way of doing this is to mimic the operation that you want to query a status for, and then find the example of that SMQ by running the built in All Status Messages SMQ for the last hour.  Once you've identified the type of SMQ you're interested in it becomes easier to set the criteria of the query.

The main take away from this example is the technique of limiting the time window within which you want the query to run. e.g. you only want to know about a certain type of event that happened 7 days ago between 13:00 and 16:00.

Right click Status Message Queries and click Create Status Message Query

Click Edit Query and enter a Name and a comment

On the general tab use the yellow sun button to add any additional Class\Attributes.  By default there are three class \ attribute types of Status Messages <All> , Status Message Strings <All>, Status Message Properties <All>

Click the Criteria tab

Use the yellow sun button to add a criteria

Select Prmopted Value from the Criterion Type dropdown

Click Select and choose a Message Class and attribute.  For this example choose Status Messages \ Message ID

Leave the operator as "is equal to"  and Prompt will be automatically set to <prompted value>

The next two criteria enable you to choose the time windows you are interested in.

Add a criteria for a prompted value of Status Message\Created On.  Set the operator to "is greater than or equal to".   This causes a prompt for the earliest (the furthest back in time) date and time you are interested in.

Notice the criteria are automatically ANDed in the criteria window.

Next Add a criteria for a prompted value of Status Message\Created On.  Set the operator to "is less than or equal to".   This causes a prompt for the latest (the most recent in time) date and time you are interested in.

Click the OKs and Nexts and run the query by right clicking it and clicking Show Messages

You will get three items that you must enter data for:
The Message IS
The latest date/time
The earliest date/time

Click OK and check out the results!  Go query dude!  No really, go and learn how to query databases properly.  You know you should.  Maybe the Show Query Language button will give you some clues?