Searching...
Filters
SmallMediumLarge
Home Print Show Topic URL Previous Next
IT Management Suite
Client Management Suite
Server Management Suite
Deployment Solution
Asset Management Suite
ServiceDesk

Creating a custom audit report for Adobe using Query Builder

Client Management Suite

After you enable application metering, you can use Query Builder to create a custom report in the Symantec Management Console.

You can also build a custom report using a plain-text SQL query.

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

For more information, see the topics about creating custom Notification Server reports in the IT Management Suite Administration Guide.

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.

To create a new report and add tables and associations

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

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

  3. Under Software/Applications, right-click Software, and then click New > Report > Computer Report.

  4. On the New Computer Report page, type a new name for this report.

    For example, type Adobe audit (Query Builder).

  5. On the Query tab, click Base Query, and then, on the right, in the Base Resource Type drop-down list, click Software Component.

    When a dialog box opens, click OK.

  6. On the Query tab, under Base Query, click Software Component, and then, under Actions, click Create Joins.

  7. In the Joins dialog box, create a table join as follows:

    Inner join Inv_AddRemoveProgram on [Software Component].[Guid] = _SoftwareComponentGuid.

    adobe11.png

    Click OK.

  8. On the Query tab, under Base Query, click Software Component, and then, on the right, under Actions, click Use Resource Type Associations.

  9. In the Resource Type Associations dialog box, in the drop-down list, click [Software Component Contains File] to [File], and then click OK.

  10. On the Query tab, under Software Component Contains, click File, and then, on the right, under Actions, click Create Joins.

  11. In the Joins dialog box, create a table join as follows:

    Left Outer join Inv_Monthly_summary on [File].[Guid] = FileResourceGuid.

    adobe12.png

    Click OK.

  12. On the Query tab, under Software Component Contains, click File, and then, on the right, under Actions, click Create Joins.

  13. In the Joins dialog box, create a table join as follows:

    LeftOuter join Inv_Software_Execution on [File].[Guid] = _ResourceGuid.

    adobe13.png

    Click OK.

  14. Click Save Changes.

Next, add the third table, which will be used to get the count of computers that can be metered. You must then add a filter expression that lets you exclude the operating systems that are not supported by the Application Metering Plug-in.

In this example, you filter the results by GUID f5758af1-eb77-436f-b63f-e75473cf3c09, which is a GUID of the Windows Computers with Application Metering plug-in.

To join the CollectionMembership table and add a filter expression

  1. On the Query tab, under Base Query, click Inv_AddRemoveProgram, and then, in the right pane, under Actions, click Create Joins.

  2. In the Joins dialog box, create a table join as follows:

    Left Outer join CollectionMembership on [Inv_AddRemoveProgram].[_ResourceGuid] = ResourceGuid.

    adobe14.png

    Click OK.

  3. On the Filter Expressions tab, click Switch to Advanced Mode.

  4. In the left pane, click [CollectionMembership] Join Filters.

  5. In the right pane, in the drop-down list, click Equals.

    When a dialog box opens, click OK.

  6. For the first filter operand, under Filter Expression Operands, in the {0}:: drop-down list, click Field, and then, in the right drop-down list, click [CollectionMembership].[CollectionGuid].

    For the second operand, under Filter Expression Operands, in the {1}:: drop-down list, click Text, and then, to the text box on the right, paste the following GUID:

    f5758af1-eb77-436f-b63f-e75473cf3c09

    adobe15.png

  7. In the left pane, click [Inv_Software_Execution] Join Filters.

  8. In the right pane, in the drop-down list, click Equals.

    When a dialog box opens, click OK.

  9. For the first filter operand, under Filter Expression Operands, in the {0}: drop-down list, click Field, and then, in the right drop-down list, click [Inv_Software_Execution].[IsMetered].

  10. For the second operand, under Filter Expression Operands, in the {1}: drop-down list, click Integer, and then, to the text box on the right, type 1.

    adobe16.png

    Note that after you change the Alias for [Inv_Software_Execution].[IsMetered] to IsMetred on the Field tab, [Inv_Software_Execution].[IsMetered] will automatically change to [IsMetered].

    adobe17.png

  11. Click Save changes.

In the next step, you choose the table fields that will appear in the report. You also aggregate data and choose the sort order.

The following list shows the fields that you must add to the report when you perform the next procedure.

[Inv_AddRemoveProgram].[DisplayName]

Displays the software name, as shown in the Windows Add/Remove Programs window.

In the Alias box, type Name.

[Inv_AddRemoveProgram].[Publisher]

Displays the software publisher, as shown in the Windows Add/Remove Programs window.

In the Alias box, type Publisher.

[Inv_AddRemoveProgram].[_ResourceGuid]

Displays the count of computers (both servers and workstations) with this software installed.

In the Alias box, type Installed. In the Aggregate drop-down list, click Count Distinct.

[Inv_Software_Execution].[IsMetered]

Displays the count of workstations with this software installed.

In the Alias box, type IsMetered.

In the Aggregate drop-down list, click Count Distinct.

[Inv_Monthly_Summary].[_ResourceGuid]

Displays the count of workstations on which the software has been run.

In the Alias box, type Used.

In the Aggregate drop-down list, click Count Distinct.

(Optional) In the Sort Direction drop-down list, click Descending.

To add fields to the report

  1. Open the report that you want to edit, and then click the Fields tab.

  2. On the Fields tab, on the toolbar, click the Add symbol, and then add the fields that are shown in the left column in the table on the Fields tab.

    You can add fields one by one. You can also check Select Multiple Fields, and then add multiple fields from the drop-down list.

    After you add the fields, you can configure them as shown in the table below.

    adobe18.png

  3. On the Fields tab, remove all other fields except for [CollectionMembership].[CollectionGuid] that are not part of this list. Click a row, and then, on the toolbar, click the Delete symbol.

  4. Click Save changes.

[CollectionMembership].[CollectionGuid] is a required hidden field in this example, and it cannot be removed.

You can add a parameter to the report that lets you filter the results by the software publisher. First, you add a new parameter text box to the report, and then you configure the report query.

To add a parameter using Query Builder

  1. Open the report you want to edit, and then click the Report Parameters tab.

  2. On the Report Parameters tab, on the toolbar, click Add > New Parameter.

  3. In the Editing Parameter dialog box, fill in the following text boxes:

    Name

    Type Publisher.

    Descrpiption

    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, in the Label Text box, type Publisher.

  5. Click OK.

  6. Click the Data Source tab, and then click the Query Parameters tab.

  7. On the Query Parameters tab, on the toolbar, click Add > Publisher.

  8. On the Filter Expressions tab, in the left pane, click [Inv_AddRemoveProgram] Join Filters.

  9. In the right pane, in the drop-down list, click Like.

    When a dialog box opens, click OK to confirm.

  10. For the first filter operand, in the {0}: drop-down list, click Field and then, in the right drop-down list, click [Publisher].

    For the second operand, in the {1}: drop-down list, click Parameter, and then, in the right drop-down list, click Publisher.

    adobe19.png

  11. Click Save changes.