Sample Compliance report with filters, groups, calculations, charts, and conditional styles

This example demonstrates how to create a Compliance report that shows the percentage for noncompliance of servers for an OS type and version for the latest server runs. This report also shows the trend of percentage of noncompliance for OS type and version.

To create a Compliance report with filters, groups, calculations, charts, and conditional styles

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 Server/OS Name
   • Compliance Server/OS Version
   • Compliance Server/Server Name
   • Compliance Template(Policy)/Template(Policy) Name
   • Compliance Job Run Date/Calendar Date
   • Compliance Job/Job Name
   • Compliance Job/Job Run Start Time
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 noncompliant 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 noncompliance 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 OS Name, OS Version, Server Name, and Template(Policy)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 latest server runs.
11. In the Order field, verify that 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.
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 Template (Policy) 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 Non Compliant
    • Compliance Rule Result/Rules Indeterminate
    • Compliance Rule Result/Rules Checked
18. Apply the addition operation on the Rules Non Compliant and the Rules Indeterminate columns as follows:
    1. Select the Rules Non Compliant and the Rules Indeterminate 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 Non Compliant + Rules Indeterminate is added.
19. Apply the Percentage operation on the Rules Non Compliant + Rules Indeterminate and Rules Checked columns.
    A new column, % ((Rules Non Compliant + Rules Indeterminate), Rules Checked), is added.
20. Rename the % ((Rules Non Compliant + Rules Indeterminate), Rules Checked) column as % Non 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 Non Compliant
    • Rules Indeterminate
    • Rules Checked
    • Rules Non Compliant + Rules Indeterminate
22. Apply conditional styles to the % Non Compliance column as follows:
    • If the value is more than or equal to 33%, the column cell appears in green.
    • If the value is less than 33%, the column cell appears in red.
23. Create a bar chart that plots the OS Name, OS Version data against % Non compliance.
24. Click the Run Report link.
25. Click the Run with All Data link.
26. 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