Multiple audit records per one report refresh

[Former Member]

We use BO XI R3 on Windows with CMS on DB2.

I need to create audit reports on BO report usage with following data:

username, timestamp, duration, report name for DeskI and WebI reports.

I use auditing on DesktopIntelligenceCacheServer and WebIntelligenceProcessingServer.

The problem is that I'm getting multiple records in AUDIT_EVENT table with different event_ids and timestamps per one refreshed report.

Following SQL brings 2 records from WebI server all the time (first with duration=0) and from 1 to 5 records from DeskI server, all of them with real report duration.

SELECT

start_timestamp,

ae.EVENT_ID,

user_name,

duration,

object_type,

detail_id,

detail_text

FROM BO_XI_R3.AUDIT_EVENT ae,

bo_xi_r3.AUDIT_DETAIL ad,

bo_xi_r3.EVENT_TYPE et

where ae.EVENT_TYPE_ID=et.EVENT_TYPE_ID

and ae.EVENT_ID=ad.EVENT_ID

and ad.DETAIL_TYPE_ID=8

and ae.EVENT_TYPE_ID=19

order by 1;

How to separate unique refresh info?

Edited by: Valentin Volk on Oct 3, 2008 9:03 PM

[Former Member · Accepted solution]

Hi Valentin,

The BO CMS stores the audit record for the reports with refresh duration = 0 by default. We had the similar situation and always we used to append REFRESH_DURATION != 0 in our audit sql, thus removing the duplicates from the audit query output.

[Former Member]

Old thread but we are getting the same issue where some event "Document refreshed" are what seems to be duplicate. I would say maybe 30-40% of them seems to have some kind of start and end entry in the audit DB. Has any of you found a solution or explication to this?

Best regards

[Former Member]

Valentin,

The "duplicate" records that you are seeing, is it always consistent or just sometimes. I ran you query against my Auditor database and sometimes (like maybe less than 50%) I am seeing "duplicate" records. I say "duplicate" because the Event_ID is different in each case, but by sorty by start_timestamp, I can see a duration 0 record, and if the query takes like 5 seconds, then five seconds later I see the entry again (with a new Event_ID) and the second record has a duration of 5. What does all this mean? I don't know exactly, other than BO sees the act of submitting a report (sometimes) as an activity (and records an "enter" record with a duration of zero, zero for obvious reasons), then when the report ends another entry to the journal used to record the duration. And at other times I don't see the "enter" record, just the entry with the duration. In my practice we run a similiar query as you've provided but we do not record where duration is zero.

thanks,

John

[Former Member]

John,

I have results that you got just for WebI reports and it's possible to pick one of them with duration >0 (of course if real duration >= 1 sec) but my major problem is DeskI reports were I got from 1 to 5 records which were identical just timestamp and event_id differed.

[Former Member]

Valentin,

My apologies, in your first posting you mentioned DeskI reports and I totally overlooked that. The reason why I'm not seeing the 5-some entries that you are is that we don't normally run DeskI, our entire user population is web only. Sorry about missing the DeskI portion in your post, thus I can't comment further on the situation, but yet it is a mystery to you. Best wishes.

Thanks,

John