Better Subselect Queries
Posted by Jarvis on January 30, 2010
Earlier this week was the January meeting of the Minnesota System Center User Group. Our content this month was centered around writing queries and troubleshooting tips. During the discussion, I was demonstrating how to create a subselect query using the ConfigMgr GUI. This was something that I blogged about almost two years ago…mainly as a thank you to Rod Trent for a fantastic post that he had done way back in 2001. Rod’s post put the creation of subselect queries on the bottom shelf for those of us who aren’t strong in SQL. Note: a subselect query is a “not” query…i.e. return all systems that do not have xyz.
One of the really cool things about our user group is that we have two guys who are Microsoft MVPs for ConfigMgr…Brian Mason and John Nelson from Wells Fargo. If you know anything about John Nelson…it’s that he is a freak of nature when it comes to creating insane queries in ConfigMgr. And yes…in this instance the term “freak of nature” is a high compliment!
So…frequently as we would talk about a particular query or way of searching for something…after we had a workable query, we would ask for input from John on how to improve the query. He had lots of very useful input…including on subselect queries. After the meeting while talking with Brian and John, we discussed creating an updated post on subselect queries…so here goes…
Rod’s post will get you a totally working query. The issues that John brought up were mainly centered around performance. Note that the guys at Wells Fargo have to be VERY concerned about performance in everything they do. Their client count and database size is very very large…it is essential that they have every query working as efficiently as possible!
Now…in Rod’s post, he has you create two queries. In the Result Properties for both queries, Rod uses the System Attribute Class and the Name attribute. What John pointed out to us is that this attribute is of variable length and is not indexed which makes the query require more processing power/time. He pointed out that using the SystemResource attribute class and the ResourceID attribute would give significantly faster results. The reason for this is that the ResourceID attribute is a 4 byte integer that is indexed in the database.
Another aspect that was mentioned was what to search for on the Criteria tab. In Rod’s demonstration post he searched on msaccess.exe…something that is found via Software Inventory. The preferred criteria would come from the Hardware Inventory…for software titles, this would be the Add/Remove Programs attribute (which is part of Hardware Inventory).
By the way…please don’t misinterpret this post as a slam on Rod in any way. Rod is a friend and someone who has done a ton for the management community for a very long time. Rod’s original post wasn’t meant to be a “best practice” method, but more of a tutorial on how to create a subselect query. This post is simply intended to update that tutorial information with different criteria that you will want to consider using in a production environment in order to get better performance.
Below is the step by step process for creating subselect queries utilizing the criteria mentioned above. Most of the step by step process below comes from Rod’s post…any step by step of a process is going to be nearly identical. I have changed key parts to reflect the better performance notes mentioned above. I have also created a short video tutorial at the end of this post that walks through these steps so you can see it. (Note: the video player requires Flash.)
Create the “Does Have” query. This will be used by the subselect query.
- Right click on Queries, select New, then Query.
- Give your query a name…something like All Systems with Microsoft Project
- Click on Edit Query Statement
- In the General tab click the Add button (the yellow star).
- In the Result Properties dialog click on the Select button.
- Select SystemResource under Attribute Class then select the ResourceID attribute.
- Click OK.
- Click OK again to close the Result Properties dialog.
- Next select the Criteria tab and click the add button.
- In the Criterion Properties dialog click the Select button.
- In the Select Attribute dialog select the Attribute class Add/Remove Programs and choose Display Name as the Attribute.
- Click OK.
- Back in Criterion Properties leave the Operator set to is equal to and type Microsoft Office Project Professional 2007 in the Value field.
- Click OK to close the Criterion Properties dialog.
- Click OK to close the Query Statement Properties dialog.
- Click Next then click Finish.
That query shows all computers that DO have Project 2007 installed. This will be used by the subselect query to not include those systems in the final results.
Create the Subselect Query:
- Right click on Queries, select New, then Query.
- Give your query a name like All Systems without Microsoft Project.
- Click on Edit Query Statement.
- In the General tab click the Add button (the yellow star).
- In the Result Properties dialog click on the Select button.
- Select SystemResource under Attribute Class then select the ResourceID attribute.
- Click OK.
- Click OK again to close the Result Properties dialog.
- Next select the Criteria tab and click the Add button.
- Use the Criterion Type pull down to select Subselected values as your Criterion Type.
- Click the Select button.
- In the Select Attribute dialog select the Attribute class System Resource and choose Resource ID as the Attribute. (Note that you used Resource ID as the results for your first query.)
- Click OK.
- Back in Criterion Properties set the Operator to is not in.
- Below that, click Browse under Subselect.
- In the Browse Query dialog select the All Systems with Microsoft Project query that you just created.
- Click OK. You’ll see the WQL code from your first query is pulled into your subselect statement for this query. If you click the Show Query Language button you will see the actual WQL subselect query.
- Click OK, OK, Next, Finish.
Better Subselect Queries « The Realm of the Verbal Processor - Rod Trent at myITforum.com said
[...] Better Subselect Queries « The Realm of the Verbal Processor Filed under: System Center, ConfigMgr 2007 [...]
Bill Bradley said
Not sure what I would do with the Resource ID, as, I would most often use this to create a collection to deploy to, and, it’s not very user-friendly.
I guess you can have a collection of resource ideas and do stuff to it?
In Rod’s writeup of this, he mentione using NetBIOS name, rather than Name–how’d that change to Resource ID?
The video demo was helpful–appreicated your doing that
Thanks.
Jarvis said
While fine-tuning the query you could add a name field so you ensure you are getting the right systems. After that, the collection with ResourceIDs will be more efficient and yes you can advertise to that collection.
In Rod’s post he uses System.Name not Netbios name. Where did you see him using NetBIOS name? Using ResourceID came from the recommendation from John Nelson mentioned in the post…because that field is a 4 byte integer that is indexed in the database.
Glad you liked the video. I thought it would be helpful to some…I know I went bug-eyed a couple of times following detailed instructions like that. Seeing it makes it a lot easier to understand.
Bill Bradley said
Now, of course, I can’t find…
I thought I had read something that said “instead of name, use netbios name”.
Thanks for the info on using Resource ID’s, I might try that!
Bill Bradley said
Found it: “Jarvis showed the basic idea of a subselect and #2 reminded folks to always use the NetBIOS name over name ” it was in the write-up on the Minnesota User Group’s page.
Stevie Lamb said
Jarvis, you are a God-send.
John sounds like the man in the know – respect to him, definitely – but you’re the man who made the knowledge available…
Have had COnfigMgr here for about 2 months, all good, nice and healthy after a recent overhaul, but this particular feature’s correct usage has escaped me until now.
And that’s having just finished the Microsoft course “Planning, Deploying and Managing Microsoft System Centre Configuration Manager 2007″…
So you’ve saved me countless repetitive software install and configure tasks. CHEERS!!!
Jarvis said
Wow…thanks for the high compliments.
I definitely want to make note that before publishing this post I typed it up and emailed it to John to make sure I was accurately representing what we talked about that night. And while I’m the one that posted this particular post, everyone really should take a look at John’s blog…he has a TON of very useful information. Just click on his name in the post above.
Dan said
First off, thanks for the excellent blog and kudos to the uber cool communtity ya have here!
Now, down to business.. =)
I created the following subselect query and ran it. (Sorry, I just cant send out a job to resource id’s I used names. We are a medium-small shop so I think the extra overhead wont hurt me)
select SMS_R_System.NetbiosName from SMS_R_System where SMS_R_System.NetbiosName not in (select distinct SMS_R_System.NetbiosName from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = “MSASCui.exe”)
Now, when I run this subselect query it returns machines that do have the file “MSASCui.exe” on them. Do I have big problems somewhere? If so what steps should I take to get this data return corrected..
Thanks in advance for any help you may provide.
Regards,
Dan
Jarvis said
A couple of thoughts. First, you need to not have the “distinct” in the query. Second, be aware that this is all dependent on when the last time inventory was run.
I think the “distinct” in your query is the problem. I don’t have any data in my demo environment to test against, so I can’t confirm for sure.
Dan said
Hi Jarvis,
I read a comment where someone said use netbios name. I have built this query.. probably 6 + times. This was the latest itineration, there was one without distinct, one that used system name and one using resource ids. For this query I added “distinct” becuase the first stage query was returning duplicates and I thought that might be what was causing problems.
I will build it again, and my software inventory runs nightly. Im thinkiing that machines that have sick sccm clients might show up in this query because there isnt a “Good” software inventory for that machine so it appears to not have the “MSASCui.exe” file I am looking for.
Thanks again Jarvis!
Regards,
Dan
Bill Bradley said
I’d like to know if NOT using Distinct is correct. I know issues occur if both the original query and the subselect query don’t have the SAME settings under the General Tab (like both use JUST name, or if name and something else used, both use both). I’ve used Distinct without issue, and, it sometimes seems to actually make the query more accurate.
I’ve always thought of it as the old Venn Diagrams in gradeschool (remember “Set Theory”?). You have the “Whole”, then you have two mutually exclusive sets OF the Whole. So, the General Tab, and part of the criteria need to be identical as they reference the Whole, they’ll deviate at at least one point, maybe just one point, in the criteria. If your Whole is client = 1, OS like “%workstation%”, then both queries need to include those things, and give a result of Name (or NetBIOS Name, or Resource ID), then you diverge.
I didn’t find that Distinct gave wrong results, but, don’t know, for sure, so, would like to find out from the Guru’s!
Rob said
This works perfectly when trying to exclude software, as in the example – much better than methods I used in my old 2003 environment. However Im now trying to establish some new methods of working in a 2007 environment and it isnt working for me.
Im trying to produce a query to show all machines except those that are listed in an AD group. The first query to select the machines in the AD group works fine, and when this is applied to the subselect queries, it imports the correct WQL code. However the query doesnt execute, and gives the error [*The ConfigMgr Provider reported an error. *]
The WQL from the Subselect query looks something like this….
select SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System where SMS_R_System.SystemGroupName = "MyDomain\\GroupOfComputers")
If anyone can provide assistance in getting this type of subselect working it would be greatly appreciated!
Rob said
A bit more googling came up with an answer to my issue above, which I thought I should post here. (Note that I changed it from a query using Name to ResourceID based on the notes above
select SMS_R_System.Name, SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceID from SMS_R_System where SMS_R_System.SystemGroupName = "MyDomain\\GroupOfComputers")
Why this works but the first query I tried doesnt is a mystery to me.