Which Business Objects Auditing Activities or Events Capture BO Universe Name?

Posted on December 3, 2008 by Julian 53 Comments

Some of us in the in the wide world of BO use universes as a method to segregate our reporting. This is logical since a universe is usually tied to a single database connection. Therefore little groups of reporting users tend to congregate around universes. Some of us have picked up on this little fact and we require a naming standard that allows us to identify those groups. Some may try to do this with report names, but most fail. Ok, let me get to the point…

In such a scenario you may want to track the activities of these groups. This is possible with Business Objects auditing services and the universe name recorded there; however, it does not work in every way you want it to. Universe is not a field in the auditing database tables as username or start_timestamp are. Universe will be found in the big melting pot that BO calls the Audit_Detail table. It is one of the many values that bless the detail_text field. Not bad news, but this will hurt your query performance.

So now that we know where to find the universe name in the auditing data, we need to talk about which Events or User Activities it accompanies. First the bad news: you won’t find it on any of the following BO Auditor event types: get page, edit document, or read document. This is bad news, but get over it, there is nothing you can do about it. So now for the good news: you will find it on Document Refresh and Generate SQL.

Just in case you are not already intimately familiar with these events let me tell you something about them:

Document Refresh: this is the interactive user refresh of a report, not the scheduled refresh

Generate SQL: this seems to be limited to saved changes to a query, or clicks on the “View SQL” button. Therefore, this may be an indicator of the amount of document editing and creation going on.

Please reply with your comments, I would love to hear them (especially if I have made a mistake).

Enjoyed this post? Share it!

53 thoughts on “Which Business Objects Auditing Activities or Events Capture BO Universe Name?”

Mareswar on February 25, 2010 at 11:10 am said:

Hi,

I am modifying the job summary report, where i want object name,user name,folderpath(Detail Type Description),status and object type(schedule output type)

I am able to get the details of object name,user name,folderpath(Detail Type Description),status with query filters Event Type Id inlist(327681;327682) and Detail Type Id equal to 43.

if add object type to the report,the data vanishes.

can you please suggest how can get schedule out put format for example excel,pdf etc.. with the query conditions Event Type Id inlist(327681;327682) and Detail Type Id equal to 43.
Julian on February 25, 2010 at 12:34 pm said:

I will look into this soon and get back to you. Could you post your SQL, it would make it easier for both of us. Thanks.
Vanathi on March 24, 2011 at 9:39 am said:

Hi,

I am trying to find Document refresh event for a WebI report(Which is not scheduled).Please suggest what are all the objects I need to select from activity universe?

I Checked WebIntelligence report Server Audit settings also.It has the document refresh option enabled.But am not able to see the results.

In the report I selected Universe name,Action Name,Action Time,Document Name objects and applied filter on Action Name = Document Refresh and Universe Name.

But it retrieves Scheduled reports details only.

Please suggest.

Thanks,
Vanathi K
Julian on March 25, 2011 at 9:21 pm said:

Hi Vanathi, the Auditing universe is full of derived tables it is hard to get what you really want. For more control, I suggest you add all of the Auditing tables ad create classes for each (you will need to define joins, but you can figure those out). With this you will be able to get just what you want. Audit_Event, Audit_Detail, Event_Type are all you really need.
Vanathi on March 27, 2011 at 9:19 am said:

Hi Julian,

Thanks for your prompt response.

I will try as you suggested.

Thanks,
Vanathi.
erika_yanina on May 30, 2011 at 10:36 am said:

Hi, I was wondering if audit database needs some kind of maintenance or improvement to performace after some years using BO auditing?
Julian on June 1, 2011 at 7:26 am said:

Hi Erika, I suggest creating Audit_Event_Archive and Audit_Detail_Archive tables. Then I would move all of the Audit Events (with their corresponding Audit Detail records) older than 1 year to these tables. This will boost your performance after an index rebuild and table stats update (Oracle).

Other than this, all you can do is try to leave the data and just rebuild the indexes and update the table stats and you may see performance increase.

If you are using the “Activity” universe provided by Business Objects then your queries might heavily depend on their inefficient derived tables in that universe. You might see improved performance by creating a new universe on just the auditing tables and working directly with them.
Alisha on July 6, 2011 at 9:37 am said:

Hi,

I am trying to find the duration of a report execution, meaning how long did the report actually ran on any particular day.

So, related to that, I have a question regarding AuditEvent.Duration object in the Activity universe. Is this the right object that I am looking for? I checked the Auditor Guide and it just says, “Duration, in seconds, of the action that is audited.” Can you throw some light further into understanding how the duration is calculated in this object.

All our universes are set for a 10 min query execution limit, but the values in this objects gives me 5 digit numbers (10894 secs)which is ~ 181.5 mins … which doesnt makes sense to me.

I hope my question is clear. Please let me know if you have any questions for me.

Please help!

Thanks,
Alisha.
Julian on July 7, 2011 at 7:56 pm said:

Hi Alisha, firstly, I don’t trust the Activity universe much. It is probably accurate, but it is quite inefficient. I suggest adding the real tables to the universe and creating the joins between then the data model is quite easily discerned. If you are looking at “report refresh” (#19) audit events then you should be seeing durations in seconds, yes. Can you query the tables directly to check that the values are not being fudged up by the Activity universe? Also, which version of BO are you using? When I here back from you I will take a closer look at my test environment’s auditing.
Alisha on July 12, 2011 at 9:06 am said:

Julian,

I am curently working on getting access to Audit Database. I will query the database as you said once I have my access. Currently in my environment here … we have diff derived tables with custom sql for extracting report name, universe name, object name, viewed report name, edited report name etc … with filters on event type ids and detail type ids for each derived table.

We are using XIR3.1 with audit database on oracle 10.

Let me know if you need any additional information.

Thanks much !!!!!
Alisha.
Julian on July 12, 2011 at 12:12 pm said:

Access to the database is not necessary if you have access to Designer and the Activity universe. You could simply add the tables to that universe, or create a new one using the same connection. Then you would have all that you need. One of these days I should create a universe that is just based on the auditing data structures and not on any derived tables. I really don’t like those derived tables very much.
cherry on December 9, 2011 at 3:03 pm said:

Julian,

I’m trying to pull all the universes connection names using
SELECT SI_NAME, SI_ID, SI_FILES, SI_DATACONNECTION FROM CI_APPOBJECTS WHERE SI_KIND = ‘universe’

but using the above query, i’m getting only the universe name instead of universe name and connection name.

Could you please look into this and assist me ASAP
Julian on December 15, 2011 at 6:39 am said:

Hi Cherry, try using si_kind = 'MetaData.DataConnection'.
Brat on December 16, 2011 at 6:18 pm said:

I am using BO XI R2 I enabled all the Audit check-boxes BUT no data is getting populated. Any ideas? The tables AUDIT_EVENT, AUDIT_DETAIL are all empty. I presume these are the tables where I can find universe usage, report usage, user audit etc.
Julian on December 17, 2011 at 6:31 am said:

Hi Brat, are you certain that the auditing DB is correctly configured through CCM, and that all servers in the cluster running CMS are properly configured to be able to connect to this auditing database? Your tables and expectations are correct, by the way. I would also recommend a full system restart after confirming my first questions.
Brat on December 19, 2011 at 11:32 am said:

Thanks for your response. If you ask me if its correctly configured, can you please let me know what you mean? The ccm cms are all functioning properly for many months now and no problem with business objects reporting. I recently switched on the audit as I wanted to develop some custom admin universe and reports to track usage but the corresponding tables did not get populated. Do you think i need to check anything else to ensure its configured correctly. I have enabled all the auditing on all servers if thats what you mean??
Again, Thanks Julian for your reply.
Brat on December 19, 2011 at 12:27 pm said:

Guess what – its working now, i had to restart twice thats it. 🙂
However can you also please let me know which are the primary keys to link between tables so I can know audit it. information like who logged on when, which univ was accessed, which report was created, which fact table was queried etc
abhi on March 18, 2012 at 11:47 pm said:

Hi,

Can we also track on Report Run times and/or Peak load usage / ?

Thats what we are after to find out on what the activity is like in CMS and find out what will be peak times when reports are run and which reports? along with The report run time from Start to Finish populating the records ?

This is getting quite critical for us as we need to goto the bottom of it to avoid bottlenecks in prod system when it goes live.

So effectively we are just after 3 things

1. Start and End time of report run

2. Peak usage time for reports that have run.

3. Which reports have run the max no. of times.

Please advise,
Julian on March 19, 2012 at 7:30 pm said:

Hi Abhi, all of this is possible with auditing enabled.
1) report query refresh duration is recorded
2) You can easily mine this from the header records (Audit_Event)
3) You can easily mine this from the header records (Audit_Event)
I need to write an article that goes more in-depth on this. I hope I can get to this soon, but it may be a while coming.
abhi on March 19, 2012 at 7:51 pm said:

Hi Julian,

thanks for the info,

Can you please suggest the field names from where I can get this info for each report ?

I know DURATION field from AUDIT_EVENT but at present that field is empty in our Audit Data. Dunno why ?

Is that Duration in Secs or milliSeconds ?

For Peak Usage times and Which report have run max no. of times you suggested to get from Audit_event – Can you provide me with the SQL query or so for that ? Please

We are using BO XI 3.1 SP2 on Solaris Oracle 10.2

Regards
abhi
Sunil on March 29, 2012 at 4:09 am said:

Hi Julian,

I am creating a audit report where the requirment is to have report names or else count of report which have scheduled successfuly and scheduled which have failed where i am using the Action_name_id in (327681;327682) which are working fine. But i also need how many reports which are schedule is currently in running state and pending state. Is there any Action_name_id(Event_type_id) value which can caputure such events.
Julian on March 29, 2012 at 6:51 am said:

Hi Sunil, great question!

You have found the fine line between auditing and CMS metadata. Actually, there is a little overlap in that line. The CMS also stores instances that have completed (failed or successful), but they disappear from the CMS if any instance limit is applied to them. The pending and running instances are only available in the CMS InfoStore, meaning you can’t query them with a normal database query. You must use either our beloved Query Builder or the Business Objects SDK. One of these days I will write some SDK code I could publish that extracts this data, but that won’t help you right now.
Julian on March 29, 2012 at 7:12 am said:

Hi Abhi, I thought I replied to your latest questions, but I don’t see it.

Duration is in seconds, I believe. If it is empty, I am not really sure why that would be, please try a refresh that you know takes at least a few seconds and then look later for the auditing record for that refresh.

Peak Usage, hmm… from the top of my head how about this:
SELECT TRUNC(ae.start_timestamp,'HH24'), COUNT(1) FROM Audit_Event ae, Event_Type et WHERE ae.event_type_id = et.event_type_id AND et.event_type_description = 'Document Refreshed' AND ae.start_timestamp > TRUNC(SYSDATE,'DDD') - 30 GROUP BY TRUNC(ae.start_timestamp,'HH24') ORDER BY TRUNC(ae.start_timestamp,'HH24') ASC
That would give you the count of reports for each hour of the day looking back at that last 30 days.

I have not tested that query, please do test and report back. Also, I should state that this query will work with XI 3.1. The “refresh” description would need to be something else that escapes me right now. This query won’t work at ALL in BI 4.0. The auditing data model changes quite a bit in BI 4.0 (a.k.a. XI 4.0). I should write an article about both XI and BI auditing data models.
Bharad on March 29, 2012 at 1:04 pm said:

Hi Julian
You are doing an excellent job of helping fellow BO architects. Thanks.
I want to know more about CI_ID, SU_ID, a general flow diagram maybe of all tables and joins, that kind of information. Can you help?
Also I dont see any CI_*** tables all I see is AP_** tables, is it because we are looking at different versions, mine is XI R2.
Thanks
Julian on March 29, 2012 at 8:53 pm said:

Hi Bharad, the “CI” tables are logical tables stored int he CMS InfoStore. You will only see “CI_InfoObjects”, “CI_AppObjectS” and “CI_SystemObjects” if you use the admin tool called “Query Builder” (it is a Java web application) or if you use the Business Objects Software Development Kit (SDK). These tables store metadata and generally all auditing tables are stored in normal database tables that you could query with any database tool.
abhi on March 29, 2012 at 10:28 pm said:

Thanks heaps julian for your feedback..
I havent tried your recommendation yet but will soon…

however I was thinking why cant i get same info about report size. run times from QueryBuilder ?

CMS metadata also stores all info about all the repors / wid / etc in here so any processing happening at CMS gets logged at SI_Infobjects6 table isnt it ?

Can i get the same infor from here ?

regards
abhi
Julian on March 29, 2012 at 10:55 pm said:

Hi Abhi, CI_InfoObjects stores a lot of information about the reports, but it is metadata. The closest thing to auditing is information about completed report instances. Number of refreshes, when, who, what… all of that is ONLY recorded in the auditing data.
Bharad on March 30, 2012 at 3:36 pm said:

Julian, got it. thanks. I used query builder and understand what you are talking about. But I got Two questions – I queried on many crystal and webi reports and the SI_UNIVERSE columns value is usually empty or contains some random value, how can I get the actual name of the universe the report is connecting to? to which table should i join to find this information ? I am looking for the universe name, database connection etc. And basically then find out how many reports in the repository use this universe using another query.
Appreciate your help, thanks !!
venkatasatya on April 9, 2012 at 11:39 am said:

Hi,
I am looking at to get the users accounts mappped through LDAP to BOXI. We need account created date. I have seen the acitivity universe is giving only the user account used in running the objects also the account dropped too but not created. Is there any way I can get this information from my Audit acitivity universe in BOXI3.1.

Please let me know
Julian on April 9, 2012 at 3:01 pm said:

No venkatasatya, that data is not going to be in the auditing/log tables. Business Objects only stores user creation date in the metadata which is stored in the CMS InfoStore. You can retrieve this data either through CMC or through the Query Builder tool.
Mythun on April 23, 2012 at 9:41 am said:

My clients is on BO 4.0 and its authentication is thru Windows AD. Client wanted to know how many users were successfully logged. I used the universe, but was not able to see all the users. I see 5 users (administrators etc who logged in other way than thru portal) but not getting the user info who are logging thru the web URL. But when i create report using the event type “view” then I can see the event activity for users. How to get the logon event?
abhi on April 24, 2012 at 1:39 am said:

HI Julian,
I tried using your supplied code as follows:-
SELECT
TRUNC(ae.start_timestamp,’HH24′),
COUNT(1)
FROM
Audit_Event ae,
Event_Type et
WHERE
ae.event_type_id = et.event_type_id
and
ae.object_type = ‘Crystal Reports’
AND
–et.event_type_description = ‘Document Refreshed’ AND
ae.start_timestamp > TRUNC(SYSDATE,’DDD’) – 30
GROUP BY
TRUNC(ae.start_timestamp,’HH24′)
ORDER BY
TRUNC(ae.start_timestamp,’HH24′) ASC

However, I was not able to see much statistics based on “Document Refreshed”.

I guess that wont apply in our case as we dont have instances where one refreshes the report or view reports through INFOVIEW.
Reports are viewed and run by WEBSHPERE API.
So I tried using “Objects Created” / “Report Created” all in vain.

Can you please advise on what EVENT TYPE should i be using ?

I have enabled all events under CMS / RAS servers / CRYSTAL PROCESSING SERVER / CRYSTAL CACHE SERVER.

I am not getting the results that I want.

Your thoughts ?
ansz5 on June 20, 2012 at 7:02 am said:

Julian,

Can you please guide how to extract information related to report schedules or report instance – like type ( Recurring/Scheduled on Demand) ?

thanks in anticipation.
Julian on June 22, 2012 at 8:45 am said:

Hi ansz5, the data you seek is not contained very well in the auditing data. Please remember that BusinessObjects auditing data ONLY contains data about things that already happened. Therefore a report schedule that cannot by its nature exist int he auditing data because it is a piece of programming that is set to run future instance(s) of a report.

Report schedules can be retrieved from the CMS InfoStore (repository database); however, you cannot query this wealth of knowledge directly. You need to use “Query Builder” or the Business Objects SDK to access this data. In Query Builder you could run a query such as this one to retrieve schedules:
SELECT * FROM CI_InfoObjects WHERE si_instance = 1 AND si_recurring = 1

The property “si_instance” will be equal to “1” for all schedules and instances and “si_recurring” will be equal to “1” for scheduled reports only. So if you want just instances and NOT schedules then try this modification:

SELECT * FROM CI_InfoObjects WHERE si_instance = 1 AND si_recurring = 0

If you want to know more about Query Builder you will find many articles published here and we sell an extensive and popular Query Builder guide as well on the site (link).
KK on August 9, 2012 at 5:28 am said:

Hi,

In BOXIR2 is it possible to trigger an event when a report runs i.e.when the report gets triggered, it will trigger the event and not when the report finish. Is there any way we can achieve this.

Regards,
KK
Julian on August 9, 2012 at 6:37 am said:

Hi KK, BusinessObjects events are my weakness, unfortunately. I couldn’t tell you if that were possible.
Evester on October 14, 2012 at 8:26 pm said:

When trying to use the Activity universe in 3.1, the objects from the drived tables will not parse. Especially using the conver(“column name”. char). When I change it around with the data type first, it parses in the universe, but will not run when I use the object in Web Intelligence. Any suggestion on how to get these objects from the derived tables to run will be greatly appreciated.
Julian on October 22, 2012 at 6:35 am said:

Hi Everster, which database are you using? I have never noticed this problem, just I have ceased to use the kludgey Activity universe mostly as it does not work well with any system that gets a lot of use.
prashant on January 1, 2013 at 9:09 pm said:

In BI4, using auditing is it possible to get a list of Data providers (Bex query names) that each webi report is based of?

I am able to get the universe names, not all my reports are based of a unx.
Nilesh on March 25, 2013 at 1:11 am said:

Hi

We are using XIR3.1 with audit database on oracle 10.
We Want to see who all are using our universes and how much. Like how many times particluar user has used X universe. Users generally use rich client for creation of adhoc reports on various universes.

Appreicate your help.
Julian on March 29, 2013 at 2:08 pm said:

Hi Nilesh, the “Document Refresh” or “Report Refresh” event captures the universe, even if the report is not saved. So I would look to this one (I think it is event_type_id = 19). Then you can look for the universe (this is detail_type_id = 2). Here is a SQL statement that I have used on Oracle which might serve you:
SELECT ae.event_id, ae.user_name, ae.start_timestamp, et.event_type_description, dt.detail_type_description, TRIM(TO_CHAR(ad.detail_text)) detail_text FROM Audit_Event ae, Audit_Detail ad, Event_Type et, Detail_Type dt WHERE ae.event_id = ad.event_id AND ae.server_cuid = ad.server_cuid AND ae.event_type_id = et.event_type_id AND et.event_type_description = 'Document Refreshed' AND ad.detail_type_id = dt.detail_type_id AND dt.detail_type_description = 'Universe name' AND TRIM(TO_CHAR(ad.detail_text)) = 'My-Universe-Name' ORDER BY ae.start_timestamp DESC, ae.event_id
Varma on June 12, 2013 at 4:29 am said:

Hi,
Is there a way to find out the list of reports (deski/webi) that are using personal data providers (excel or free hand sql) other than universes from audit universe.

Env – BOXI R2
Julian on June 13, 2013 at 12:08 am said:

Hi Varma, I do not have a BusinessObjects XI R2 environment to test this any longer. However, I invite you to look closely at the details of a document refresh with a universe and one with Free Hand SQL. I am betting there is a difference in some of the Audit Details. Additionally, you may choose to look through Query Builder (again comparing two different documents and finding the differences. Please do report back your findings when you have a moment.
pius on November 20, 2013 at 12:37 am said:

Hi julian,

Nice work, but i want list of all report refreshed for 1 day with report name, report type and Universe name and refreshed ID
pius on November 20, 2013 at 12:44 am said:

Hi julian i report the above information from auditing DB or if i can get from the macro
Nilesh on April 8, 2014 at 8:03 am said:

Hi julian,

We are trying to find out CUID’s of all full client report which are located in a particular folder. Would you please suggest on query using activity universe? I have tried out using object CUID, Object name and Object Type but not getting any data. Thanks
Anuradha on September 24, 2014 at 5:16 am said:

Hi Julian,

We are using BO4.1 SP3.
Have never worked on Audit Universe before. There are a lot of changes from 3.1 I think.

Please let me know if I need to create a Derived table to get information like Viewed_report_names, Edited_report_names, Refreshed_report_names, Accessed_universe names or these can be directly extracted.

If I need to create derived tables, please let me know the detail_type_id/Event_type_ID values for different events

Thanks in advance!!!
Julian on September 29, 2014 at 9:43 pm said:

Hi Anuradha, A derived table is a good idea. I wish I had one developed already which I could provide to you. Perhaps I need to spend some time on this and build a nice article on the topic.
Bhanu on November 11, 2014 at 5:04 am said:

Hi Julian,

Im trying to create the audit reports based on the audit_event audit_detail and event_type tables. I need the information of the reports which are not run atleast once.

Im able to retreive the data based on number of instances of the reports in the audit tables.

But also, i need to retreive the details for Zero run reports.

Please suggest
Thanks in advance.
Julian on November 11, 2014 at 11:17 pm said:

Hi Bhanu, “Zero run” reports are not possible. These are auditing tables which audit actions. Think of them as log files loaded to tables. If a report is never run then it won’t be in the log files. To achieve what you want you would have to extract a list of all reports from the CMS repository and then subtract from it all of the reports in your auditing tables. Extracting a list of all reports can be done through Query Builder or through the SDK. Either solution is not easy.

53 thoughts on “Which Business Objects Auditing Activities or Events Capture BO Universe Name?”

Leave a comment Cancel reply