Symantec recommends that you save the report regularly while making changes to the report. This reduces the chances of the Symantec Management Console timing out and losing your changes. To save the changes you made to the report, you can click either Save Changes or Apply. To return to the report, click Edit.
The query that you use in this example is as follows:
COUNT(DISTINCT ifd._ResourceGuid) AS 'Installed',
COUNT(DISTINCT cm.Metered) AS 'Metered',
COUNT(DISTINCT eas._ResourceGuid) AS 'Used'
FROM ( SELECT DISTINCT _ResourceGuid, DisplayName, Publisher, parentresourceguid, ChildResourceGuid FROM Inv_AddRemoveProgram iarp JOIN ResourceAssociation ra ON iarp._SoftwareComponentGuid = ra.ParentResourceGuid WHERE Publisher LIKE '%adobe%' ) ifd
LEFT JOIN ( SELECT DISTINCT resourceguid AS Metered FROM CollectionMembership WHERE CollectionGuid = 'f5758af1-eb77-436f-b63f-e75473cf3c09' ) cm ON cm.Metered = ifd._ResourceGuid
LEFT JOIN ( SELECT DISTINCT _ResourceGuid, FileResourceGuid FROM Evt_Application_Start ) eas ON eas.FileResourceGuid = ifd.ChildResourceGuid AND eas._ResourceGuid = ifd._ResourceGuid
This query selects all software resources that display Adobe as the publisher in the Windows Add/Remove Program dialog box (WHERE Publisher LIKE '%adobe%'). The count of computers with this software will be displayed in the Installed column. You will replace the %Adobe% substring with a report parameter later in the process.
In the LEFT JOIN statement that follows, the query gets the count of computers that can be metered. Inventory Solution can collect inventory from both server and workstation operating systems, but application metering is available for workstations only. When you run the report, the count of workstations with this software is displayed in the Metered column. This particular example uses the collection that is used by the default application metering policy. Note that if you use a non-default target to meter Adobe software, the data in the Metered column will be inaccurate. If the data is inaccurate, you can further customize the report.
The last LEFT JOIN statement gets the count of computers on which an application from Adobe was executed and displays it as Used.
This is a simplified query and it does not let you specify a time interval for which to display metering data. You can add these parameters later.
To create a custom audit report for Adobe using plain-text SQL
In the Symantec Management Console, on the Reports menu, click All Reports.
In the left pane, under Reports, expand Discovery and Inventory > Inventory > Cross-platform > Software/Applications.
Under Software/Applications, right-click the Software folder, and then click New > Report > SQL Report.
On New SQL Report page, rename the report.
For example, rename the report to Adobe audit (SQL).
Click the Parameterised Query tab, and then, in the text box, delete all the default query text.
Copy the SQL query that is provided in this topic and paste it into the text box.
Click Save Changes.
To add a parameter to the plain-text SQL report
On the report page, click the Report Parameters tab.
On the toolbar, click Add > New Parameter.
In the Editing Parameter dialog box, configure the following settings:
Under Value Provider, in the Name drop-down list, click Basic Parameter Value Edit Control, and then, under Configuration, in the Label Text box, type Publisher.
On the report page, click the Data Source tab, and then click the Query Parameters tab.
On the toolbar, click Add > Publisher.
Click the Parameterised Query tab.
In the text box, before the query, add the following lines:
DECLARE @v3_Publisher nvarchar(max)
SET @v3_Publisher = N'%Publisher%'
In the SQL query, locate the following string:
and replace it with the following:
Click Save Changes.
To test the report, you can type %oracle% in the Publisher box. Then refresh the report, and see if it displays the list of Oracle software that is discovered by Inventory Solution.