When ConfigMgr administrators are creating a collection query to find all computers with a particular application for both x86 and x64 computers, almost everyone falls into the same mistake. That mistake is querying for both x86 and x64 software titles using an Inner Join between x86 and x64 views. When I point out this mistake, I often get different answers. Here’s the last one I got about this issue: “This [query] doesn’t depend on your PC or OS architecture (x86 or x64).”
The operating system (OS) architecture is very important to your query. Why? Well, OS architecture is important because an x86 computer does not have any entries for x64. Okay, but why is that important? We will get to that, but first let me show you the Resource Explorer for two computers, Win2k8 (x86) and Win81-cm4 (x64). I will use these two computers in my explanation.
Notice that the purple arrow points to the Installed Applications in both screenshots, (SMS_G_System_ADD_REMOVE_PROGRAMS), the gold arrow points to Installed Applications (64) (SMS_G_System_ADD_REMOVE_PROGRAMS_64), and the green arrow points to Monitor Information Reporting v5 in both screenshots.
Now, as I said, most ConfigMgr admins will write their query this way:
Notice the results of the query below.
At first glance, these results may look correct, but see how there are two inner joins within the query? This means that a computer MUST have information with both SMS_G_System_ADD_REMOVE_PROGRAMS and SMS_G_System_ADD_REMOVE_PROGRAMS_64 WQL views.
However, you saw above in Resource Explorer that Win2k8 only has information for the SMS_G_System_ADD_REMOVE_PROGRAMS view. Therefore, this computer will never be displayed in the results using the query above.
To help prove my point, let’s review the next two queries. Notice that the first one will find all x86 computers with Monitor Information Reporting v5 and the second query will do the same for x64 computers.
Below are the results for both queries. Notice that the x86 query only has one computer and the x64 has three computers. Compare these results with the combined query (inner join) above, and you can see that Win2k8 is missing from the list. This is because the computer is x86 and does not have any x64 software installed on it.
How can you ensure that you are always getting accurate results? Well, if the query is for a collection, then the simplest way is to use two queries to populate the collection; both are listed above. Most people, however, only want to use a single query. In this case, you will need to learn about subselect queries.
See my previous blog posts on the subject:
Subselect Query for SCCM Queries or Collections (WQL)
Below is the subselect query version for both x86 and x64 computers.
You can see that with this query, Win2k8 is listed within the results as expected.
I hope this helps clear up the issue of having a single query to locate both x86 and x64 software titles. If you have any questions about OS architecture and queries, please feel free to contact me at @GarthMJ.
Can you provide the actual text and not a graphic so I can copy and paste please.
Something like below?
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 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 like “%Reader%”
Thanks Shawn. yup that was an earier blog where I was trying NOT to let WP formating “mess” things up. Later blogs, I fixed that. I plan to update Many of the blogs there to “fix” everything as I have time.