How to Create a Custom Software Product License Compliance Report

Products

• Asset Management Solution

Issue/Introduction

 

Resolution

The Software Product Licensing Compliance report under Reports>Service and Asset Management>Contract Management>Software Licensing is useful for searching for a single software product and comparing the number of installs to licenses assigned to the software product. However there is no report that can show this data for a select list of software products. To create a report like this go to the Reports page and right-click on a desired folder, then select New>Report>SQL Report. Give the report an appropriate name and then paste and modify the following SQL query over the example query in the Parameterized Query box. Please note that Symantec Support does not support custom reports so further modifications must be made by the user. Click Save Changes to view the results. Also note that the Inv_SoftwareProduct_ComplianceInfo table is automatically updated only once-per-day so to manually update the table run Software Product Licensing Recalculation Operation Task under Manage>Jobs and Tasks, then System Jobs and Tasks>Service and Asset Management>Contract Management.

SELECT SP.Name, 
SC.[Purchased Licenses],
COUNT (vComputer.Name) [Installs]
FROM Inv_SoftwareProduct_ComplianceInfo SC
LEFT JOIN RM_ResourceSoftware_Product SP ON SP.Guid = SC._ResourceGuid
LEFT JOIN ResourceAssociation RA ON RA.ParentResourceGuid = SP.Guid
LEFT JOIN Inv_InstalledSoftware SW ON SW._SoftwareComponentGuid = RA.ChildResourceGuid 
LEFT JOIN vComputer ON vComputer.Guid = SW._ResourceGuid
WHERE RA.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
AND (
SP.Name like 'Microsoft Office Standard 2010'
OR SP.Name like 'Adobe Acrobat Pro%' -- Add a line like this for each desired software
)
GROUP BY SP.Name, SC.[Purchased Licenses]