Sample Compliance report with filters, groups, and calculations

This example demonstrates how to create a Compliance report that shows the percentage rule compliance of servers with the templates for the latest server runs.

To create a Compliance report with filters, groups, and calculations

1. Launch Query Studio from the BMC Decision Support for Server Automation portal.
2. Select a package (for example, BladeLogic) from the list of available packages.
3. Click the Run Report link.
4. Click the Preview with No Data link.
5. Click the Insert Data link.
6. Open the Compliance folder and drag the following query items to the right pane:
   • Compliance Template(Policy)/Template(Policy) Name
   • Compliance Server/Server Name
   • Compliance Job/Job Name
   • Compliance Job/Job Run Start Time
   • Compliance Job Run Date/Calendar Date
7. To display a date range every time you run the report, apply a filter on the Calender Date column, as follows. You can view the percent compliance results for this date range.
   1. Select the Calendar Date column and click Filter on the toolbar.
   2. From the From field, select a date starting from which to view the percent compliance results.
   3. From the To field, select Latest Date.
   4. Select the Prompt every time the report runs option.
   5. Click OK.
8. Group the report by the Template(Policy) Name and Server Name columns.
9. Select the Job Run Start Time column and click Calculate on the toolbar.
10. From the Date field, select the date from which you want to retrieve the latest server runs.
11. Verify that in the Order field, the Job Run Start Time - <Date> option is selected.
12. Click Insert.
    A new column, Job Run Start Time - Date, is added.
13. Format the Job Run Start Time -Date column as follows:
    1. Right-click the column and select Format Data.
    2. From the Category list, select Interval.
    3. From the Type list, select 1 23:45:12.
    4. Click OK.
14. Apply the Rank calculation on the Job Run Start Time - Date column ,as follows:
    1. Select the column and click Calculate on the toolbar.
    2. From the Operation type list, select Analytic.
    3. From the Operation list, select Rank.
    4. From the Options field, select the Based on values within each group option, and from the list, select Server Name.
    5. Click Insert.
       A new column, Rank (Job Run Start Time - Date) is added.
15. Apply the filter on the Rank (Job Run Start Time - Date) column as follows:
    1. Select the column and click Filter on the toolbar.
    2. In the From and To fields, type 1.
    3. Click OK.
16. In the Combine filters dialog box, click OK.
17. Open the Compliance folder and drag the following query items to the right pane:
    • Compliance Rule Result/Rules Compliant
    • Compliance Rule Result/Rules Compliant with Exception
    • Compliance Rule Result/Rules Checked
18. Apply the addition operation on the Rules Compliant and the Rules Compliant with Exception columns as follows:
    1. Select the Rules Compliant and the Rules Compliant with Exception columns, and click Calculate on the toolbar.
    2. Ensure that from the Operation type list, Arithmetic is selected.
    3. Ensure that from the Operation list, addition is selected.
    4. Click OK.
       A new column, Rules Compliant + Rules Compliant with Exception, is added.
19. Apply the Percentage operation on the Rules Compliant + Rules Compliant with Exception and Rules Checked columns.
    A new column, % ((Rules Compliant + Rules Compliant with Exception), Rules Checked), is added.
20. Rename the % ((Rules Compliant + Rules Compliant with Exception), Rules Checked) column as % Compliance.
21. Delete the following columns without deleting the associated filters:
    • Job Run Start Time - Date
    • Rank (Job Run Start Time - Date)
    • Calender Date
    • Rules Compliant
    • Rules Compliant with Exception
    • Rules Checked
    • Rules Compliant + Rules Compliant with Exception
22. Click the Run Report link.
23. Click the Run with All Data link.
24. Save the report.
    The following figure shows a sample Compliance report created by using this procedure:
    

Related topics

Sample reports demonstrating Query Studio features
Creating reports with Report Studio