Using BI Publisher to Create Audit Reports

Once auditing is configured, you can use BI Publisher to create your own reports to visualize your auditing data.

To create a report on your auditing data in BI Publisher:

1. Register the data source in BI Publisher.
2. Create a data model.
3. Create the report.

Registering the Data Source in BI Publisher

Register the audit data source (JNDI/JDBC connection) that you created in the previous procedure as a JNDI data source in BI Publisher.

Because you created a JDBC connection registered as JNDI, you do not need to create a new JDBC connection by typing the connection URL, username/password, and so on. You can just register it using the JNDI name (for example: jdbc/AuditDB).

1. Log in to BI Publisher with administrator privileges and click the Administration link.
2. Under Data Sources, click JNDI Connection, then click Add Data Source.
3. Enter the Data Source Name and JNDI Name jdbc/AuditViewDataSource.
4. Click Test Connection to ensure that the data source connection works.
5. Add the appropriate roles to the data source so that the report developers and consumers can view the reports built on this data source.
6. Click Apply to save.

Creating a Data Model

Run SQL queries against the BIPUBLISHER_V view to create data models.

To create a data model from your auditing data source:

Note:

For the complete guidelines for developing data models in BI Publisher, see Data Modeling Guide for Oracle Business Intelligence Publisher.

1. On the global header, click New and then click Data Model.
2. Set the Default Data Source to the audit JNDI data source.
3. Click Data Sets and from the Create New menu select new SQL Query data set.
4. Use the Query Builder to build a query or just type a SQL query against the BIPUBLISHER_V view.

   The following sample SQL query returns only BI Publisher audit data common to all event types:

   Select IAU_EVENTCATEGORY,
          IAU_EVENTTYPE,
          IAU_EVENTSTATUS,
          to_char("IAU_TSTZORIGINATING", 'DD-MON-YYYY HH24:MI:SSxFF') as IAU_DATETIME,
          IAU_INITIATOR,
          IAU_RESOURCE,
          IAU_MESSAGETEXT
   from BIPUBLISHER_V;
   

   Auditing records different attributes depending on the event category and type. You can create separate queries for each event type or category to fetch event category or type specific attributes.

   The sample query below returns the audit records of resource access such as creating a report, deleting a data source, or renaming a folder. For a complete list of attributes recorded for each event type, see Audit Events in Oracle Business Intelligence Publisher.

   SELECT IAU_EVENTCATEGORY,
          IAU_EVENTTYPE,
          to_char("IAU_TSTZORIGINATING", 'DD-MON-YYYY HH24:MI:SSxFF') as IAU_DATETIME,
          IAU_INITIATOR, 
          IAU_EVENTSTATUS,
          IAU_FAILURECODE, 
          IAU_MESSAGETEXT,
          IAU_RESOURCE,
          RESOURCETYPE,
          RESOURCESUBTYPE,
          NEWPATH,
          NEWNAME
   from BIPUBLISHER_V where IAU_EVENTCATEGORY = 'ResourceAccess'
   

5. To test your data model, click View Data. Select a sample size, and run your data model. Save the sample XML to your data model.
6. Save your data model.

Creating the Report

Now you can use one of the BI Publisher's layout options to design the report layout and visualize the auditing data.

To create a report using the BI Publisher layout editor

1. On the global header, click New and then click Report.
2. Select the data model you created in the previous procedure.
3. Follow the instructions in the Create Report Wizard to create a report.

   For complete instructions on using the layout editor, see Creating BI Publisher Layout Templates in Report Designer's Guide for Oracle Business Intelligence Publisher.