How to Visualize your Audit Data with BI Publisher?

Visualize_Audit 

Do you know how many reports on your BI Publisher server are accessed yesterday ? Or, how many users accessed to the reports yesterday, or what are the average number of the users accessed to the reports during the week vs. weekend or morning vs. afternoon ?

With BI Publisher 11G, now you can audit your user’s reports access and understand the state of the reporting environment at your server, each user, or each report level. At the previous post I’ve talked about what the BI Publisher’s auditing functionality and how to enable it so that BI Publisher can start collecting such data. (How to Audit and Monitor BI Publisher Reports Access?)Now, how can you visualize such auditing data to have a better understanding and gain more insights?

With Fusion Middleware Audit Framework you have an option to store the auditing data into a database instead of a log file, which is the default option. Once you enable the database storage option, that means you have your auditing data (or, user report access data) in your database tables, now no brainer, you can start visualize the data, create reports, analyze, and share with BI Publisher.

So, first, let’s take a look on how to enable the database storage option for the auditing data.

How to Feed the Auditing Data into Database

First you need to create a database schema for Fusion Middleware Audit Framework with RCU (Repository Creation Utility). If you have already installed BI Publisher 11G you should be familiar with this RCU. It creates any database schema necessary to run any Fusion Middleware products including BI stuff. And you can use the same RCU that you used for your BI or BI Publisher installation to create this Audit schema.

Create Audit Schema with RCU

Here are the steps:

  1. Go to $RCU_HOME/bin and execute the ‘rcu’ command
  2. Choose Create at the starting screen and click Next.
  3. Enter your database details and click Next.
  4. Choose the option to create a new prefix, for example ‘BIP’, ‘KAN’, etc.
  5. Select 'Audit Services' from the list of schemas. image
  6. Click Next and accept the tablespace creation.
  7. Click Finish to start the process.

After this, there should be following three Audit related schema created in your database.

  • <prefix>_IAU (e.g. KAN_IAU)
  • <prefix>_IAU_APPEND (e.g. KAN_IAU_APPEND)
  • <prefix>_IAU_VIEWER (e.g. KAN_IAU_VIEWER)

Setup Datasource at WebLogic

After you create a database schema for your auditing data, now you need to create a JDBC connection on your WebLogic Server so the Audit Framework can access to the database schema that was created with the RCU with the previous step.

image

  • Click ‘Lock & Edit’ so that you can make changes

image

  • Click New –> ‘Generic Datasource’ to create a new data source.
  • Enter the following details for the new data source: 
    Name: Enter a name such as Audit Data Source-0. 
    JNDI Name: jdbc/AuditDB 
    Database Type: Oracle 

image

  • Click Next and select ‘Oracle's Driver (Thin XA) Versions: 9.0.1 or later’ as Database Driver (if you’re using Oracle database), and click Next.
  • The Connection Properties page appears. Enter the following information:
    Database Name: Enter the name of the database (SID) to which you will connect.
    Host Name: Enter the hostname of the database. 
    Port: Enter the database port. 
    Database User Name: This is the name of the audit schema that you created in
    RCU. The suffix is always IAU for the audit schema. For example, if you gave
    the prefix as ‘BIP’, then the schema name would be ‘KAN_IAU’. 
    Password: This is the password for the audit schema that you created in RCU.

image 

  • Click Next.
  • Accept the defaults, and click Test Configuration to verify the connection.
  • Click Next
  • Check listed servers where you want to make this JDBC connection available.

image

  • Click ‘Finish’ !

After that, make sure you click ‘Activate Changes’ at the left hand side top to take the new JDBC connection in effect.

image

Register your Audit Data Storing Database to your Domain

Finally, you can register the JNDI/JDBC datasource as your Auditing data storage with Fusion Middleware Control (EM). Here are the steps:

1. Login to Fusion Middleware Control

2. Navigate to Weblogic Domain, right click on ‘bifoundation…..’, select Security, then Audit Store.

image

3. Click the searchlight icon next to the Datasource JNDI Name field.

4.Select the Audit JNDI/JDBC datasource you created in the previous step in the pop-up window and click OK.

image

5. Click Apply to continue.

6. Restart the whole WebLogic Servers in the domain.

After this, now the BI Publisher should start feeding all the auditing data into the database table called ‘IAU_BASE’. Try login to BI Publisher and open a couple of reports, you should see the activity audited in the ‘IAU_BASE’ table. If not working, you might want to check the log file, which is located at $BI_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/logs/AdminServer-diagnostic.log

to see if there is any error.

Once you have the data in the database table, now, it’s time to visualize with BI Publisher reports!

Create a First BI Publisher Auditing Report

Register Auditing Datasource as JNDI datasource

First thing you need to do is to register the audit datasource (JNDI/JDBC connection) you created in the previous step as JNDI data source at BI Publisher. It is a JDBC connection registered as JNDI, that means you don’t need to create a new JDBC connection by typing the connection URL, username/password, etc. You can just register it using the JNDI name. (e.g. jdbc/AuditDB)

  1. Login to BI Publisher as Administrator (e.g. weblogic)
  2. Go to Administration Page
  3. Click ‘JNDI Connection’ under Data Sources and Click ‘New’image
  4. Type Data Source Name and JNDI Name. The JNDI Name is the one you created in the WebLogic Console as the auditing datasource. (e.g. jdbc/AuditDB)
  5. Click ‘Test Connection’ to make sure the datasource connection works.
  6. Provide appropriate roles so that the report developers or viewers can share this data source to view reports.
  7. Click ‘Apply’ to save.

Create Data Model

image

  1. Select Data Model from the tool bar menu ‘New’
  2. Set ‘Default Data Source’ to the audit JNDI data source you have created in the previous step.
  3. Select ‘SQL Query’ for your data set
  4. Use Query Builder to build a query or just type a sql query. Either way, the table you want to report against is ‘IAU_BASE’. This IAU_BASE table contains all the auditing data for other products running on the WebLogic Server such as JPS, OID, etc. So, if you care only specific to BI Publisher then you want to filter by using  ‘IAU_COMPONENTTYPE’ column which contains the product name (e.g. ’xmlpserver’ for BI Publisher).

Here is my sample sql query.

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    "KAN3_IAU"."IAU_BASE" "IAU_BASE"
where "IAU_BASE"."IAU_COMPONENTTYPE" = 'xmlpserver'

Once you saved a sample XML for this data model, now you can create a report with this data model.

Create Report

image

Now you can use one of the BI Publisher’s layout options to design the report layout and visualize the auditing data. I’m a big fan of Online Layout Editor, it’s just so easy and simple to create reports, and on top of that, all the reports created with Online Layout Editor has the Interactive View with automatic data linking and filtering feature without any setting or coding. If you haven’t checked the Interactive View or Online Layout Editor you might want to check these previous blog posts. (Interactive Reporting with BI Publisher 11G, Interactive Master Detail Report Just A Few Clicks Away!) But of course, you can use other layout design option such as RTF template.

Here are some sample screenshots of my report design with Online Layout Editor.

image

 

image

 

Visualize and Gain More Insights about your Customers (Users) !

image

Now you can visualize your auditing data to have better understanding and gain more insights about your reporting environment you manage. It’s been actually helping me personally to answer the  questios like below. 

  • How many reports are accessed or opened yesterday, today, last week ?
  • Who is accessing which report at what time ?
  • What are the time windows when the most of the reports access happening ?
  • What are the most viewed reports ?
  • Who are the active users ?
  • What are the # of reports access or user access trend for the last month, last 6 months, last 12 months, etc ?

I was talking with one of the best concierge in the world at this hotel the other day, and he was telling me that the best concierge knows about their customers inside-out therefore they can provide a very private service that is customized to each customer to meet each customer’s specific needs. Well, this is true when it comes to how to administrate and manage your reporting environment, right ? The best way to serve your customers (report users, including both viewers and developers) is to understand how they use, what they use, when they use. Auditing is not just about compliance, but it’s the way to improve the customer service. The BI Publisher 11G Auditing feature enables just that to help you understand your customers better.

Happy customer service, be the best reporting concierge!

p.s. please share with us on what other information would be helpful for you for the auditing! Always, any feedback is a great value and inspiration for us!  

Follow bipublisher on Twitter

Comments:

Hi Kan,

Thanks for posting this good information.

i have couple of doubts
1) how to create these schema
<prefix>_IAU (e.g. KAN_IAU)
<prefix>_IAU_APPEND (e.g. KAN_IAU_APPEND)
<prefix>_IAU_VIEWER (e.g. KAN_IAU_VIEWER)
I have created KAN_IAU using RCU but how to create reaming two schema because when i tried to create these two from RCU.i got error "RCU-6016:The specified prefix already exists."

Do i need to create remaining two directly in databse.

2)what is the role of all three schema in the context of Auditing and monitoring.

I did not create last two schema and followed all the remaining steps But database table is not feeding up whereas i can see entries in audit.log
it would be great if you help me on it.

Thanks in advance,
Mohit

Posted by mohit on August 04, 2011 at 01:51 AM MDT #

Hi Kan,

Thanks for posting this good information.

i have couple of doubts
1) how to create these schema
<prefix>_IAU (e.g. KAN_IAU)
<prefix>_IAU_APPEND (e.g. KAN_IAU_APPEND)
<prefix>_IAU_VIEWER (e.g. KAN_IAU_VIEWER)
I have created KAN_IAU using RCU but how to create reaming two schema because when i tried to create these two from RCU.i got error "RCU-6016:The specified prefix already exists."

Do i need to create remaining two directly in databse.

2)what is the role of all three schema in the context of Auditing and monitoring.

I did not create last two schema and followed all the remaining steps But database table is not feeding up whereas i can see entries in audit.log
it would be great if you help me on it.

Thanks in advance,
Mohit

Posted by guest on August 04, 2011 at 02:38 AM MDT #

Those schemas should be created when you create IAU schema with the RCU. I just tested this with the one I downloaded from the OTN and it did create those 3 schemas.

Posted by Kan Nishida on August 04, 2011 at 07:59 AM MDT #

Thanks for prompt respose;

Yes..i also checked and schemas are there in DB.but at time of creation KAN_IAU final screen did not show me that these three schemas has been created.so i was in impression that rest two schemas has to be created.

Anyways..i have followed all steps(including Run ‘xmlpserver_audit.sql’ ) to enable the Audit data and visualize (except report creation) and as result i can see the entries in audit.log but data is not feeding in 'IAU_BASE'.

Could you pls tell where should i check.(I have checked AdminServer-diagnostic.log .but could not find anything relevant)

Pls suggest

Regards,
Mohit

Posted by mohi on August 07, 2011 at 09:11 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today