Using BI Publisher to Create Audit Reports

Once you have the auditing repository set up, 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. The JNDI Name is the name you provided in the WebLogic Console as the auditing data source (for example: jdbc/AuditDB).
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 IAU_BASE table to create data models.

To create a data model from your auditing data source:

Note:

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 IAU_BASE table. The IAU_BASE table contains all the auditing data for other products running on the WebLogic Server such as JPS, OID, and so on. To create a data model that contains only the BI Publisher data, you can filter the data based on the value of the IAU_COMPONENTTYPE column that contains the product name. For BI Publisher, the value is "xmlpserver".

   The following sample SQL query returns only BI Publisher data:

   select     "IAU_BASE"."IAU_COMPONENTTYPE" as "IAU_COMPONENTTYPE", 
        "IAU_BASE"."IAU_EVENTTYPE" as "IAU_EVENTTYPE", 
        "IAU_BASE"."IAU_EVENTCATEGORY" as "IAU_EVENTCATEGORY", 
        "IAU_BASE"."IAU_TSTZORIGINATING" as "IAU_TSTZORIGINATING", 
      to_char("IAU_TSTZORIGINATING", 'YYYY-MM-DD') IAU_DATE, 
      to_char("IAU_TSTZORIGINATING", 'DAY') as IAU_DAY, 
      to_char("IAU_TSTZORIGINATING", 'HH24') as IAU_HH24, 
      to_char("IAU_TSTZORIGINATING", 'WW') as IAU_WEEK_OF_YEAR, 
        "IAU_BASE"."IAU_INITIATOR" as "IAU_INITIATOR", 
        "IAU_BASE"."IAU_RESOURCE" as "IAU_RESOURCE", 
        "IAU_BASE"."IAU_TARGET" as "IAU_TARGET", 
        "IAU_BASE"."IAU_MESSAGETEXT" as "IAU_MESSAGETEXT", 
        "IAU_BASE"."IAU_FAILURECODE" as "IAU_FAILURECODE", 
        "IAU_BASE"."IAU_REMOTEIP" as "IAU_REMOTEIP" 
   from    "BIP_IAU"."IAU_BASE" "IAU_BASE" 
   where "IAU_BASE"."IAU_COMPONENTTYPE" = 'xmlpserver'
   

5. To test your data model, click Get XML Output. 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. To use the layout editor, click Add New Layout, and then click one of the Basic Templates to get started.

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

   The figure below shows using the layout editor to design a report based on the auditing data:

   
   

   
   Description of the illustration GUID-76F2E8FE-2D60-4C46-8E63-87F30F9759E3-default.gif

   
   

   The figure below shows a sample completed auditing report displayed in the report viewer:

   
   

   
   Description of the illustration GUID-A10E30A3-BD1D-42AC-8867-E42BB454988E-default.gif