WQL and T-SQL - different query results

Question

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%"

Answer 1

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.

Answer 2

Hi @David Zemdegs ,

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.

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.

Answer 3

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.

Answer 4

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

Answer 5

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.