Better Subselect Queries
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.
26 Comments »
Leave a comment
Subscribe
Recent Comments
-
The Realm of the Verbal Processor 
[…] Better Subselect Queries « The Realm of the Verbal Processor Filed under: System Center, ConfigMgr 2007 […]
Pingback by Better Subselect Queries « The Realm of the Verbal Processor - Rod Trent at myITforum.com | February 1, 2010
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.
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.
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!
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.
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!!!
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.
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
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.
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
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!
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!
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.
Im curently trying to write some queries for some collections to identify the servers that are not running our enterprise Symantec antivirus.
I successfully created the query to identify those that DO HAVE the installed executable, but having trouble with the subselect query.
Whenever I create the subselect query it tells me to select a valid statement and will not allow me to go further.
This is my subselect:
select SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.IPAddresses from SMS_R_System inner join SMS_G_System_INSTALLED_EXECUTABLE on SMS_G_System_INSTALLED_EXECUTABLE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_EXECUTABLE.ExecutableName = “rtvscan.exe” order by SMS_R_System.Name
The query you posted isn’t a subselect…it looks like you might have missed a step somewhere. I know the steps are tedious, but if you follow them, you will get a working subselect in the end. to see an example of a working subselect, you can refer to my other subselect post that includes an example in the middle of the post. The key phrase you will see in a subselect query is: “…not in (select…”. If you don’t see “not in” followed by a parenthetical query, then it isn’t a subselect…it’s just a query.
Hope that helps. For that matter…I’m pretty sure you aren’t allowed to have an “order by” as part of the query. That could be part of the problem as well.
Sorry I forgot to include the rest of the query.
I also removed the ordering and it allows me to continue on with the query build, but errors out when trying to run.
This is my subselct query as a whole, which includes the “not in” statement:
select SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.IPAddresses from SMS_R_System where SMS_R_System.Name not in (select SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.IPAddresses from SMS_R_System inner join SMS_G_System_INSTALLED_EXECUTABLE on SMS_G_System_INSTALLED_EXECUTABLE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_EXECUTABLE.ExecutableName = “rtvscan.exe”)
First question…are you following the step-by-step instructions or writing this from scratch?
Second, I think the problem is in your second select statement. Remove “, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.IPAddresses ” from the query and it should work. In general for a subselect, only put the specific criteria you need to get the results you are looking for. When you put it into a collection it is going to show you specific criteria about the system anyway…not just the three items you put in your query.
BTW…I saw where you work based on your email address. I’m from Mississippi originally and am an Ole Miss alum. Hotty Toddy! :-)
Ive done both the build from scratch and follow the leader. I will try and remove the other requirements and see if it works.
LOL…Yeah I work at the University of Mississippi Medical Center. I am Ole Miss Alumni as well, but seems to be rather embarrassing given the past two games!
Yeah…I was hoping for a lot more after we got Masoli. It’s been a rough year so far…and it’s not going to get any easier! Seriously…Jacksonville State and Vandy? I’m living in Minneapolis now, so I’m solidly in the heart of Big Ten country. I’m just glad for the extra coverage that the SEC gets…I can still see most games!
Hopefully removing those other two fields fixes it for you.
Success…..
Im still confused on why the list variables would keep the criteria from returning any items.
think about what it is doing… Here is what you are telling the system to do if you have three criteria:
1. Go grab fields 1, 2, and 3 for every system that X is true of.
2. Now, show me all systems whose name is not returned by the first query…except that it is being told to look for the name in a query that includes name, domain name and IP address.
It errors out because it isn’t a valid query. It is trying to compare one field to three. You need to give it concise criteria to parse.
Sup. having a lil issue i was wondering if anyone had any ideas.
First i have a collection with a query for add/remove programs.
Add/Remove Programs.Display Name is equal to “Macromedia Dreamweaver 8”
this will report about 600odd machines which i know is incorrect connecting to these machines and checking add/remove programs they don’t have it. (not sure why im getting so many results)
if i add in a SubSelected values
Where: System Resource – Resource ID
Operator: is not in
select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Macromedia Dreamweaver 8”
i get a empty collection
is it now reporting correctly that no machines have it installed. or am i just lost :)
I don’t know why you are getting those results re: Add/Remove Programs.
As for your subselect, you will need to post the entire subselect query before I could try to diagnose that one.
In our environment, we been more successful using the installed .exe string instead of the Add/Remove string. We’ve seen different versions of software make the add/remove string very compolicated and sometimes report incorrect.
I have spent an hour or so working on this subselect query. Any help would be greatly appreciated.
We want a group with all 2008 servers that does not have .net 3.5 SP1 installed. Here is what I have so far,
select distinct SMS_R_System.Name 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.Name like “Microsoft%Windows Server%2008%” and SMS_G_System_SYSTEM.Name not in (select distinct SMS_R_System.Name from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft .NET Framework 3.5 SP1”)
The above query isnt working.
Im probably going about it the wrong way. Please help =)
Thanks,
Dan
The issue with your query is partially that you are using different criteria in the two portions of the query (the two “where” clauses). Take a close look at the screencapture video on this post. You are also going one more step in query complexity by throwing in the Server 2008 limitation. I haven’t tried this, but maybe try a subselect (focused only on .NET framework) for the collection that is further restricted via a limiting collection that contains all Server 2008 systems. I don’t know if this will work or is possible…just thinking off the top of my head while in a meeting.