Creating a custom audit report for Adobe using plain-text SQL
After you enable application metering, you can create a custom report using a plain-text SQL query. You can also add parameters to an SQL query-based report.
You can use the Symantec Management Console Query Builder to create custom reports.
See Creating a custom audit report for Adobe using Query Builder.
Note: |
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 . |
The query that you use in this example is as follows:
SELECT
ifd.DisplayName,
ifd.Publisher,
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
GROUP BY
ifd.Publisher,
ifd.DisplayName
ORDER BY
Used DESC,
Publisher ASC
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.
Note: |
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 .
In the left pane, under Reports, expand Discovery and Inventory > Inventory > Cross-platform > Software/Applications.
Under Software/Applications, right-click the folder, and then click .
On 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 .
To add a parameter to the plain-text SQL report
On the report page, click the Report Parameters tab.
On the toolbar, click .
In the Editing Parameter dialog box, configure the following settings:
Under Value Provider, in the drop-down list, click , and then, under Configuration, in the box, type Publisher.
Click .
On the report page, click the Data Source tab, and then click the Query Parameters tab.
On the toolbar, click .
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:
LIKE '%adobe%'
and replace it with the following:
LIKE @v3_Publisher
Click .
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.
See Creating a drill-down computer report
Thanks for your feedback. Let us know if you have additional comments below. (requires login)