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
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).
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.
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.
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.
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.
Thanks for your prompt response.
I will try as you suggested.
Hi, I was wondering if audit database needs some kind of maintenance or improvement to performace after some years using BO auditing?
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.
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.
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.
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 !!!!!
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.
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
Hi Cherry, try using
si_kind = 'MetaData.DataConnection'.
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.
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.
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.
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
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.
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.
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
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.
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.
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:
ae.event_type_id = et.event_type_id AND
et.event_type_description = 'Document Refreshed' AND
ae.start_timestamp > TRUNC(SYSDATE,'DDD') - 30
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.
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.
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.
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 ?
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.
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 !!
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
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.
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?
I tried using your supplied code as follows:-
ae.event_type_id = et.event_type_id
ae.object_type = ‘Crystal Reports’
–et.event_type_description = ‘Document Refreshed’ AND
ae.start_timestamp > TRUNC(SYSDATE,’DDD’) – 30
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 ?
Can you please guide how to extract information related to report schedules or report instance – like type ( Recurring/Scheduled on Demand) ?
thanks in anticipation.
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:
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:
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).
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.
Hi KK, BusinessObjects events are my weakness, unfortunately. I couldn’t tell you if that were possible.
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.
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.
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.
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.
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:
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'
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
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.
Nice work, but i want list of all report refreshed for 1 day with report name, report type and Universe name and refreshed ID
Hi julian i report the above information from auditing DB or if i can get from the macro
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
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!!!
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.
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.
Thanks in advance.
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.