Custom Application Metering Report for Specific Software

book

Article ID: 178725

calendar_today

Updated On:

Products

Inventory Solution

Issue/Introduction

 

Resolution

Application Metering provides the Executable Usage and Underutilized Software reports to show when metered software was last used on each computer. However, sometimes it is not possible for Inventory Solution to properly associate all the files, versions, and software components for very complicated software and these reports become convoluted. In this case a custom SQL report can be created to try to report the desired data. The following SQL query is presented as an example that can be modified by a user with moderate SQL and Altiris experience can modify to their needs. Please note that Symantec Support does not support custom reports and provides this as is.

SELECT DISTINCT
vComputer.Name [Computer],
vComputer.[User],
A.[Last Start],
A.[File Name]
--, A.[Software Component]
--, A.[Software Product]
FROM vComputer
left JOIN
(SELECT DISTINCT Inv_InstalledSoftware._ResourceGuid,
  --RM_ResourceSoftware_Product.Name [Software Product],
  MAX(RM_ResourceSoftware_Component.Name) [Software Component],
  MAX(RM_ResourceFile.Name) [File Name],
  MAX(Inv_Monthly_Summary.[Last Start]) [Last Start]
  FROM Inv_InstalledSoftware
  LEFT JOIN RM_ResourceSoftware_Component ON RM_ResourceSoftware_Component.Guid = Inv_InstalledSoftware._SoftwareComponentGuid
  LEFT JOIN ResourceAssociation RA1 ON RA1.ParentResourceGuid = Inv_InstalledSoftware._SoftwareComponentGuid
  LEFT JOIN RM_ResourceFile ON RM_ResourceFile.Guid = RA1.ChildResourceGuid
  --LEFT JOIN ResourceAssociation RA2 ON RA2.ChildResourceGuid = Inv_InstalledSoftware._SoftwareComponentGuid
  --LEFT JOIN RM_ResourceSoftware_Product ON RM_ResourceSoftware_Product.Guid = RA2.ParentResourceGuid
  LEFT JOIN Inv_Monthly_summary ON Inv_Monthly_summary._ResourceGuid = Inv_InstalledSoftware._ResourceGuid AND Inv_Monthly_summary.FileResourceGuid = RM_ResourceFile.Guid
  --WHERE RM_ResourceSoftware_Product.Name like '%Autocad%'
  WHERE (RM_ResourceFile.Name like 'acad.exe' OR RM_ResourceFile.Name like 'acadlt.exe')
  AND RA1.ResourceAssociationTypeGuid = 'EABE86D3-AAFD-487A-AF63-5C95D7511AF6'
  --AND RA2.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483' --SW Component to Product
  GROUP BY Inv_InstalledSoftware._ResourceGuid) --, RM_ResourceSoftware_Component.Name , RM_ResourceFile.Name)
A ON A._ResourceGuid = vComputer.Guid
WHERE A.[File Name] IS NOT NULL
--WHERE (A.[Last Start] <= (GETDATE()-90) OR A.[Last Start] IS NULL) --This will show only computers that have not used the software in 90 days
--AND vComputer.Name IS NOT NULL