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