If you use the auditor package in Business Objects XI, you will very likely need to create custom versions of the canned reports to provide metrics that are useful to your specific organization.

One of the primary concerns for one of our clients was the monitoring of report run times. Given the timeout limits that are imposed on reports, if they run too long they will fail. We needed to have report stats monitored and sent out each morning after the heavy processing of the scheduled reports was completed.

As our reports run early morning, I wanted to set the report period to be 6AM yesterday to 6AM today.

In our environment our heavy hitting reports are all Web Intelligence reports so we wanted to limit reporting to Web Intel and not all jobs (as the default BO report provide).

So I  set the report to run daily at 7:00AM. Since it reports on 6AM to 6AM it will capture the scheduled report processing which is complete by 6AM.

Our environment is BusinessObjects XI Release 2 on Linux.

I looked through the canned reports that came with XI. It turns out that there weren't any canned reports that shipped with auditor that reported in a form that was useful in our environment.

Here's a list of the canned reports that are packaged with auditor:

1. Average Number of Users Logged In
2. Average Refresh Time
3. Average Session Duration
4. Average Session Duration per Cluster
5. Average Session Duration per User
6. Cluster Nodes
7. Document Information Detail
8. Document Scheduling and Viewing Status
9. Job Summary
10. Jobs per Job Server
11. Jobs per User
12. Job Servers on the System
13. Last Login for User
14. Least Accessed Documents
15. Most Accessed Documents
16. Most Active Users
17. Most Popular Actions
18. Most Popular Actions per Document
19. Number of User Sessions
20. Number of Users in the System
21. Password Modifications
22. Peak Usage
23. Refresh and Edit Activity
24. Rights Modification
25. Total Users Logged In by Day
26. User Activity
27. User Activity per Session
28. Users Who Logged Off Incorrectly

So I made a couple of simple mods to the "Activity" universe (which is what the auditor package runs off of). And I created a custom report for our environment.

The Activity universe is the universe that comes with auditor and which all the canned reports are based off of. Here's what it looks like. You can't see any of the table details, but you can see that the table structure and the universe is not very large.

I set the report to show any report failures at the top of the report, and I've set an alerter to make the entry red and bold. After failures (if any) it will sort by duration in descending order so we can see the longest running reports on top.

I set up the report to mail an excel format file.

That way, if anyone wants to sort or manipulate the data further, it will be easier.

Unfortunately when BO outputs to Excel it is next to impossible to control where BusinessObjects merges the Excel cells (vertically and horizontally). This can make it difficult to sort in excel without doing some column copy-paste-delete-editing stuff. Excel won't sort cells of uneven widths (merged and not). I tried to get "unmerged" cells in excel… which is usually an exercise in futility… and it was in this case.

Here's the major high level mods I made to set up the custom report. You will likely need to do something at least a little different.

1. Created 2 "relative date" objects in the "Activity" universe: Yesterday6AM and Today6AM . The relative date objects allow for the rolling twenty four hour reporting period.

We run in an Oracle environment so these objects were as follows:

to_date( to_char(sysdate,  'DD-MON-YYYY') || ' 06:00:00', 'DD-MON-YYYY HH24:MI:SS' )

to_date( to_char(sysdate - 1,  'DD-MON-YYYY') || ' 06:00:00', 'DD-MON-YYYY HH24:MI:SS' )

2. Copied the business objects auditor canned report "Jobs Summary" and re-worked. Main steps are below.

3. Deleted the 1st pie chart report. This report seems kinda useless unless you plan on having a large percentage of failed reports. This is not the case for us.

4. Merged the failed and successful reports onto one report. I'd rather look on one report than have to switch between two tabs on a Web Intelligence report or an Excel workbook. I used sorting and alerters to call attention to failures (as in the below steps).

5. Added Result Objects to the query as necessary. It takes a little detective work to figure out what values to filter on. BO uses numeric values that are not meaningful without some digging. I added columns to the result table and did some investigation into key values, etc. to determine how to filter.

6. Changed filtering at the Query level as below

7. [Action Name ID] = 327681;327682 (this represents the statuses of successful and failure)

8. [Action Type] = "Object Folder Path"  (this shows only the locations of the reports. since we have all the auditing options turned on each report job will be represented 3 separate times: "Object Folder Path," "Object Name," "User Groups"). We only want to represent a report one time… thus this clause in the report filter.

9. Filter on the yesterday6am and today6am to only bring in results for the desired 24 hour time period. Since the report is run daily and emailed, this is precisely what we wanted.

10. In the report itself added a filter to only show web intelligence rows, since that's what we run in our environment. [Job Server Kind] = "Web Intelligence Job Server"  (in this current XI implementation we haven't yet had the need to do much with Crystal Reports or Desktop Intelligence).

11. Arrange columns in the table as desired. Columns we wanted were: Duration, Status, Report (path and report name), and Begin Time
 

12. Created a calculated column for the status to resolve the numeric code into English:

=If([Action Name ID] = 327681; "Successful"; If([Action Name ID] = 327682; "Failed"; "Unknown" ) )

13. Added sorts on status then duration (descending)

14. Added alerters on the status to highlight failed reports and long duration reports

Some of the reports are useful as packaged with auditor, but many will need to tweak in order to be optimized for your environment. To do anything outside of the canned reports you'll need at least a rudimentary understanding of the universe.

When you are trying to filter the type of events that are shown in your custom audit report, you will need to reference the Detail_Type table. Here are the values contained.

In the above example that I walked through (reporting on reports), the "Object Folder Path" and the "Object Name" provided the most useful way to identify reports. This identifies the folder and report name.

Another useful audit meta data table is the Event_Type table. For example, in order to filter and sort based on report status (success or failure), we used the table values from the Event_Type table:

The Application_Type table provided reference to filter based on the type of server. In our case we were interested in "Web Intelligence Job Server" records.