Assets and Compliance – Device collections Part 2 (Query rule)

Part 2

Device collections as the name implies is a collection of devices, you can for example create a device collection that contains all the PC´s for the Service desk or the HR department by Active directory groups. You can create a device collection based on what OS the device is running, different manufacturer like HP, Dell or Fujitsu and even what kind of mouse they have plugged in. The limitation for what criteria you base you device collection comes down to WMI(Windows Management Instrumentation) and its query language WQL but more on that in a later post

Part 1 covered Direct rule https://timmyit.com/2016/04/28/assets-and-compliance-device-collections-part-1-direct-rule/

Query Rule

If you want to have a device collection that updates dynamically based on certain criteria then Query rule is generally the way to go, with that said you don´t have to make it update dynamically. Figuring out and configuring Query Rules tends to take a little more time at first but in the end you will save a lot of time since you don´t have to manually add resources every time there´s a new one added to your environment. In this example we are just gonna cover a simple query that will get all the Computers with a specific name just to get started and in a later post I will go in to more detail about other ways to use WQL.

Create a new Device collection (If you are unsure how, then read Part1) and under the Membership rule this time choose “Query Rule”

Device Collection Part 2 - 01

Enter the name you want to name your Query and then click on “Edit Query Statement”

Device Collection Part 2 - 02

This will open up the Query Statement Properties and here is where the magic happens, click on the “sun”

Device Collection Part 2 - 03

and then in Result properties click on “select” as seen on the right

Device Collection Part 2 - 04

Attribute class should be System Resource and under Attribute find the one called “NetBIOS Name” and click “ok”

Device Collection Part 2 - 05

In Result Properties click “ok”

Device Collection Part 2 - 06

The selection you just did should now show up in the results list and lets click on “show Query Language” to take a look at how the query looks like so far

Device Collection Part 2 - 07

The WQL Query should look like this “select SMS_R_System.NetbiosName from  SMS_R_System” this is queried against the SCCM site database to get the result we specified in the Query, so far we have only asked for “Select all the information in the column SMS_R_System.NetbiosName from the table SMS_R_System in the Site database” and for our purpose that´s to broad of a statement so lets define it a bit more to get the result we really want. Press “ok”

Device Collection Part 2 - 08
Click on “Edit Query Statement” again in the Query Rule properties

Device Collection Part 2 - 02

Click on “Criteria” in the upper left

Device Collection Part 2 - 09

Click on the “sun” to add a criteria

Device Collection Part 2 - 10

Click on “Select..”

Device Collection Part 2 - 11

Under “Attribute class” choose “System Resource” and under “Attribute” choose “NetBIOS Name” and click “ok”

Device Collection Part 2 - 12

Under “opertator” choose the options “is like” and in the Value field input SD% and press “ok”

Device Collection Part 2 - 13

The new criteria should now show up in the criteria list and lest go to “show Query Language” again to take a look at the WQL query and see what happened

Device Collection Part 2 - 14

The old Query was just “select SMS_R_System.NetbiosName from  SMS_R_System” and the new one is “select SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.NetbiosName like “SD%”” can you see what changed?

The criteria we added was “where SMS_R_System.NetbiosName like “SD%”” so we are selecting the information from the column SMS_R_System.NetbiosName from the table SMS_R_System in the site database where the NetBiosName is like “SD%” this should now only give us the computers with a computer name that starts with SD.

 

Press “ok” until you get back to the “Create device collection wizard” and take a look at “Use incremental updates for this collection” by checking this check box it will allow for the device collection to update automatically when a new devices that fulfills the requirements we set to be added to the specific device collection, if this option is not checked the update will only occur when the scheduled full update is running and its in this case every 7th day as seen below. If you uncheck “Schedule a full update on this collection” and “Use incremental updates for this collection” no newly added resources will be added to the device collection unless you do it manually by “Direct rule” as we did in Part1 or by running this Wizard again.

Device Collection Part 2 - 15

Finish the wizard with “next”, “next” and then close and the device collection should start to get filled with resources and the result should look something like this

Device Collection Part 2 - 16

And if you right-click on the device collection and choose “show members” you will see that we have only Computers with the name starting with “SD” which is exactly what we wanted.

Device Collection Part 2 - 17

Now lets add the HR departments computers to this device collection, go back to the location of the device collection and right click on and choose “properties”

Device Collection Part 2 - 18

Then click on the “Membership Rules” tab

Device Collection Part 2 - 19

Click on “Edit..”

Device Collection Part 2 - 20

Click on “Edit Query statement” as we did earlier in this guide and then click on “Show Query Language”

I sorted it a bit so we easily could see Select and From, what we are gonna do here is to add a Or criteria directly in to the Query without needing to go through the steps we did before, you could write the whole Query directly here if you could and wanted to. The statement we are going to add will look like this “or  SMS_R_System where SMS_R_System.NetbiosName like “HR%” and this will add all the computer names from the HR department since all of the their computers starts with HR in this environment .

 

Device Collection Part 2 - 21

Press “ok”, “ok”, “apply” and “ok” to get out from the properties and then give it a few seconds for the device collection to update and right click and choose “show members” and you should see the new members there

Device Collection Part 2 - 22

So this was it for this lesson and the things i showed here is just the tip of the iceberg, the possibilities and selection on what kind criteria to use for device collection are huge. I really recommend playing around with it and you really should learn more about WMI (Windows management Instrumentation) and WQL if you don´t really know what it is and how its structured because knowing that will help you understand what kind of possibilities there are.

 

Hope this was useful information for you and if you have any questions or suggestions just post them in the comments below.

Cheers,

Timmy