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,
dbo.v_R_System.Name0
ORDER BY dbo.v_Add_Remove_Programs.DisplayName0, dbo.v_Add_Remove_Programs.Version0
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
DaBXRoMeO
7 years ago
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
7 years ago
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.