Installed Software Report with Counting

[Migration User]

Hi,

for our documentation we need an overview about all installed software with following values:

- Software Name

- Software Version

- Software Company

- Count of installed Software of each Software Version

The Results shall Group By Software Name, Software Version and Order By Software Name, Software Version.

 

I tried to customize the default Installed Software report under All Reports > Discovery and Inventory > Inventory > Cross-platform > Software/Applications > Software but without success. When I try to add a counter I just get errors. Then I tried to use the Newly Discovered Software report and deactivated the since_date filter but there I get duplicates (name and version are the same but the resourceGuids are different) and it doesn't filter to take only active computer.

 

I'm at my wit's end. Any help would be great!

 

Here's the customized sql code:

  
SELECT DISTINCT sci.[Name]
               ,isc.[Version]
               ,company.[Name] [Company]
FROM vRM_Software_Component_Item sci
JOIN Inv_InstalledSoftware inst
   ON inst._SoftwareComponentGuid = sci.[Guid]
   AND inst.InstallFlag = 1
JOIN (SELECT vci.Guid, vci.Name
      FROM vRM_Computer_Item vci
      LEFT JOIN ResourceAssociation resAssoc
         ON vci.Guid = resAssoc.ParentResourceGuid
         AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
      WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
      AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))  
      ) comps
   ON comps.Guid = inst._ResourceGuid
JOIN dbo.Inv_Software_Component isc
   ON isc._ResourceGuid = sci.[Guid]
LEFT JOIN (SELECT vc.Name, ra.ParentResourceGuid AS SoftCompGuid
           FROM RM_ResourceCompany vc
           JOIN ResourceAssociation ra
           ON vc.Guid = ra.ChildResourceGuid
           AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company
   ON company.SoftCompGuid = sci.[Guid]
ORDER BY sci.Name
 

 

Thanks!

Maik

[dsmith1954]

What you want looks very similar to the Installed Software report under Reports=>Discovery and Inventory=>Inventory=>Cross-Platform=>Software/Applications=>Software.

Don't know why they make these reports so hard to find.

[Migration User]

Maik,

Give this a go i removed the trusteescope for my testing but its easy enough to add back in:

 

SELECT sci.[Name]

,isc.[Version]

,company.[Name] [Company]

,count(sci.[Name]) [Total Installed]

FROM vRM_Software_Component_Item sci

JOIN Inv_InstalledSoftware inst ON inst._SoftwareComponentGuid = sci.[Guid] AND inst.InstallFlag = 1

JOIN (SELECT vci.Guid, vci.Name FROM vRM_Computer_Item vci

LEFT JOIN ResourceAssociation resAssoc ON vci.Guid = resAssoc.ParentResourceGuid AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status

WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers 

) comps

ON comps.Guid = inst._ResourceGuid

JOIN dbo.Inv_Software_Component isc

ON isc._ResourceGuid = sci.[Guid]

LEFT JOIN (SELECT vc.Name, ra.ParentResourceGuid AS SoftCompGuid

FROM RM_ResourceCompany vc

JOIN ResourceAssociation ra

ON vc.Guid = ra.ChildResourceGuid

AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company

ON company.SoftCompGuid = sci.[Guid]

group by sci.[Name], isc.[Version], [company].[Name]

ORDER BY sci.Name

 

[Migration User]

Thanks Jim, that's it!

[Migration User]

Just a side no Maik, One of the lines there i beleive filters it so that only active machines are in the results.

You may want to remove that to show all machines regardless of if they are online or not.