Creating a custom audit report for Adobe using plain-text SQL

HOWTO110193
Last Updated February 27, 2015
Copy Article Title/URL
Feedback
Subscribe

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 Edit.

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

  1. In the Symantec Management Console, on the Reports menu, click All Reports.

  2. In the left pane, under Reports, expand Discovery and Inventory > Inventory > Cross-platform > Software/Applications.

  3. Under Software/Applications, right-click the Software folder, and then click New > Report > SQL Report.

  4. On New SQL Report page, rename the report.

    For example, rename the report to Adobe audit (SQL).

  5. Click the Parameterised Query tab, and then, in the text box, delete all the default query text.

  6. Copy the SQL query that is provided in this topic and paste it into the text box.

  7. Click Save Changes.

To add a parameter to the plain-text SQL report

  1. On the report page, click the Report Parameters tab.

  2. On the toolbar, click Add > New Parameter.

  3. In the Editing Parameter dialog box, configure the following settings:

    Name

    Type Publisher.

    Description

    Type Publisher.

    Default Value

    Type %.

    Test Value

    Type %Adobe%.

  4. 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.

  5. Click OK.

  6. On the report page, click the Data Source tab, and then click the Query Parameters tab.

  7. On the toolbar, click Add > Publisher.

  8. Click the Parameterised Query tab.

  9. In the text box, before the query, add the following lines:

    DECLARE @v3_Publisher nvarchar(max)

    SET @v3_Publisher = N'%Publisher%'

  10. In the SQL query, locate the following string:

    LIKE '%adobe%'

    and replace it with the following:

    LIKE @v3_Publisher

  11. 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.

See Creating a drill-down computer report

Imported Document ID: HOWTO110193
Legacy ID: v73210706_v106367724

Thanks for your feedback. Let us know if you have additional comments below. (requires login)

    © 1995-2019 Symantec Corporation