question

DavidZemdegs avatar image
0 Votes"
DavidZemdegs asked Garth commented

WQL and T-SQL - different query results

Greetings,

We have a collection that consists of computers that have the software 'Carbon Black' installed. The actual WQL is listed below.
We also have a collection of computers that DO NOT have the software 'Carbon Black' installed. This collection simply 'includes' all windows 10 clients and 'excludes' computers in the 'installed' collection.
What is weird is that there are a few computers that appear in the 'not installed' collection that clearly match the 'installed' query criteria. I have inspected the hardware inventory of those computers and they have the exact string being searched for in the correct attribute class.
What is weirder is that I have taken the WQL and simply converted it to T-SQL - e.g. changing SMS_R_SYSTEM to V_R_SYSTEM and changing attributes e.g. Name to Name0.
When I run that T-SQL query directly against the database it finds that computer that was in the 'not installed' collection.
IOW WQL does not find it but T-SQL does. How could this be?
WQL 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 inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Cb Protection Agent" or SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName = "Cb Protection Agent" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Carbon Black%"

mem-cm-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidZemdegs avatar image
0 Votes"
DavidZemdegs answered DavidZemdegs edited

On further investigation, the computers that appear in the 'not installed' collection that shouldnt be do not have an Installed Applications node in hardware inventory. They do have Installed Applications (x64). So it appears that querying the Installed Software attribute class is not working in WQL. Why they dont have the 'Installed Applications' node in inventory is also a mystery as it is enabled in client settings.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HanyunZhu-MSFT avatar image
0 Votes"
HanyunZhu-MSFT answered HanyunZhu-MSFT commented

Hi @DavidZemdegs,

Thanks for posting in Microsoft Q&A forum.

First, we could check the T-SQL query in SMSProv.log, confirm that there is no problem with the statement. When running the WQL query, the ConfigMgr engine will use the WMI provider to convert the WQL query into a T-SQL statement.
113110-1.png

Then, there's a possibility that the results returned by running the query only include x64 or x86 version of software.
We could modify the query to detect if the software of any version is installed.
Here is the link that can be use as reference, please check:
http://sccmug.ca/2012/01/16/wql-query-for-both-x86-and-x64-version-of-software/
Note: This is not from MS, just for your reference.

Hope the above information can help you.


If the response is helpful, please click "Accept Answer"and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



1.png (22.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Just checking in to see if there is any update. We haven't heard from you for a few days and would like to know the current status of the problem. Do you need any further assistance? Look forward to hearing from you.

Thanks for your time.

0 Votes 0 ·
DavidZemdegs avatar image
0 Votes"
DavidZemdegs answered

The current status is the same. A few computers that should satisfy the query dont. Some fix themselves after a few client reinstalls and some dont.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GarthJones-8673 avatar image
0 Votes"
GarthJones-8673 answered

What exactly does you SQL query look like?
You WQL query looks at x86 software titles.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidZemdegs avatar image
0 Votes"
DavidZemdegs answered

I dont have the SQL query any more but I simply substituted the WQL with the equivalent SQL views and changed the attribute names (usually by appending a zero).
so SMS_R_SYSTEM becomes V_R_SYSTEM, SMS_G_System_ADD_REMOVE_PROGRAMS becomes V_GS_ADD_REMOVE_PROGRAMS and SMS_G_System_INSTALLED_SOFTWARE becomes V_GS_INSTALLED_SOFTWARE.

Note that this query successfully returns thousands of computers. Its just the few that should be returned that arent that make this so weird.
And the fact that my WQL did not return them but my SQL did.
We're just reinstalling the client on a regular basis on those recalcitrant computers until they can get their act into gear.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidZemdegs avatar image
0 Votes"
DavidZemdegs answered

Here is an example of a computer that does NOT satisfy the query today.

114782-cb.jpg



cb.jpg (313.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidZemdegs avatar image
0 Votes"
DavidZemdegs answered

For the computer above here is its resourceID:

114750-rid.jpg


and here is the SQL query which returns a record where WQL does not:

114821-cbsql.jpg



rid.jpg (45.9 KiB)
cbsql.jpg (196.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Garth avatar image
0 Votes"
Garth answered Garth commented

So i will look at this in the morning when i back at the office but why are you looking at installed sw within the queries? This is bound to cause problems mixing and matching inventory types.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Part of this weird problem is that some machines did not have the 'Installed Applications' node when looking at hardware inventory. They only had 'Installed Applications (x64) and 'Installed Software'. So I had to add Installed Software to my query.

0 Votes 0 ·

Not having Installed Apps is a sign of a bigger problem. It means that your HW inventory is NOT fully working. As such I would spend time on that first before worrying about your query results.

Start by locating one of the computers without that class. Next force a FULL HW inventory on it, Make sure that the data is returned to the db.

0 Votes 0 ·

Thanks. I get that. That's why I mentioned our troubleshooting involves simply re-installing the client. And it sometimes requires a few reinstalls before the machine behaves properly. And Ive checked WMI health and even rebuilt WMI on those machines. Just a weird thing that strikes a handful of computers.

0 Votes 0 ·
Show more comments
DavidZemdegs avatar image
0 Votes"
DavidZemdegs answered

btw Running MEMCM 2006

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SherryKissinger-ECM avatar image
0 Votes"
SherryKissinger-ECM answered

This is just my past working experience talking, so take that for what it is worth.

Typically, I discourage people in my company from using "Installed Applications", and "Installed Applications 64", and instead 100% always use "Installed Software". Here's why...

Installed Apps/Apps64 is basically "what is in the registry keys, under the \Uninstall regkey branch, for 32-bit apps on a 64-bit machine, and 64-bit apps on a 64-bit machine".

Installed Software, is an Asset Intelligence class; and Presuming of course that you have enabled that (in hardware inventory) against your Default Client Settings, all clients will report "Installed Software", which will already include everything in the uninstall regkeys... as well as a few things which might not be listed THERE; but Microsoft's magical coding behind Asset Intelligence on the client (yeah, I have no clue how it really works) will also report a few additional things installed. Typically you can see the "other things" as an example when you look at v_gs_installed_software, and look for entries where ARPDisplayName0 = '' (nothing), but there is a ProductName0. Looking at one of my servers, for example, it shows "Microsoft Windows Server 2019 Standard" as "installed software", but you wouldn't see that in the ARP registry uninstall keys.

Anyway... saying all that... if it's in the uninstall regkeys... it'll be in the "Installed Software" inventory. The ONLY time people have to "use the installed applications" is when they are specifically looking for "is it 32-bit one... or the 64-bit flavor of <whatever application>". (Like Firefox and Chrome come in both 32bit and 64bit)

Sorry for the long explanation; but basically I'm saying don't mix InstalledApplications and InstalledSoftware; it's kind of messy. Just stick with InstalledSoftware unless you have a 32-bit/64-bit reason to split it out.

(off topic a bit, 2006 is a year old... might want to upgrade to the latest soon)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.