SCCM\SQL Query for specific software company not in Add\Remove Programs

I've been tasked for reporting all machines (with IP addresses) that have software from a specific company.  In this case it would be HP Enterprise.  The following query gives me results from Add\Remove Programs. However, when I run the "All inventoried products for a specific software company" in SCCM reporting I get way more machines listed.  I've been breaking my head trying to figure out how to query these machines without using ARP.  When trying to use the query from SCCM (copied into SQL) it just doesn't work.  I'm at a pretty interesting road block.  Any ideas?

SCCM 2012 v1702
SQL MS 2014

Current Query using ARP:

SELECT        TOP (100) PERCENT dbo.v_Add_Remove_Programs.DisplayName0, dbo.v_Add_Remove_Programs.Version0, COUNT(*) AS Expr1, 
                         dbo.v_Add_Remove_Programs.Publisher0, dbo.v_R_System.Name0
FROM            dbo.v_Add_Remove_Programs INNER JOIN
                         dbo.v_R_System ON dbo.v_Add_Remove_Programs.ResourceID = dbo.v_R_System.ResourceID
WHERE        (dbo.v_Add_Remove_Programs.Publisher0 LIKE N'Hewlett-Packard Enterprise%') OR
                         (dbo.v_Add_Remove_Programs.Publisher0 LIKE N'HPE%') OR
                         (dbo.v_Add_Remove_Programs.Publisher0 LIKE N'Hewlett Packard Enterprise%')
GROUP BY dbo.v_Add_Remove_Programs.DisplayName0, dbo.v_Add_Remove_Programs.Version0, dbo.v_Add_Remove_Programs.Publisher0, 
ORDER BY dbo.v_Add_Remove_Programs.DisplayName0, dbo.v_Add_Remove_Programs.Version0

0 Comments   [ + ] Show comments

Answers (2)

Posted by: DaBXRoMeO 4 years ago
Senior White Belt
Just so happens that I found what I needed after a very very long Google hole.  In case anyone needs it for future reference, this seemed to work for me.

SELECT DISTINCT TOP (100) PERCENT SYS.Netbios_Name0, SYS.Resource_Domain_OR_Workgr0, SP.CompanyName, SP.ProductName, SP.ProductVersion
FROM            dbo.v_GS_SoftwareProduct AS SP INNER JOIN
                         dbo.v_R_System AS SYS ON SP.ResourceID = SYS.ResourceID INNER JOIN
                         dbo.v_FullCollectionMembership AS fcm ON SYS.ResourceID = fcm.ResourceID
WHERE        (fcm.CollectionID = N'SMS00001') AND (SP.CompanyName LIKE N'Hewlett-Packard Enterprise%') OR
                         (SP.CompanyName LIKE N'HPE%') OR
                         (SP.CompanyName LIKE N'Hewlett Packard Enterprise%')
ORDER BY SYS.Netbios_Name0, SP.CompanyName, SP.ProductName, SP.ProductVersion
Posted by: MikhailCompo 4 years ago
White Belt
Generally speaking, for SCCM queries (WQL queries using the Console), the best way to build this is to create a query that SHOWS only machines that HAVE the software installed. Then wrap that query in a NOT IN: [aformentioned query]. This will then show results of machines that are not in the original query, which is what you require.

With SCCM 2012 onwards, if you create queries that identify machines that do not have Application X installed, it can be used for deployments (perhaps with an additional requirement of the computer being a member of an AD group?).

Hope this helps.
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ