/build/static/layout/Breadcrumb_cap_w.png
05/05/2017 7357 views
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

Comments


All Answers

0
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
Answered 05/05/2017 by: DaBXRoMeO
Senior White Belt

0
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.
Answered 05/11/2017 by: MikhailCompo
White Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share