BusinessObjects Board

Using the WEBI audit tables? Got a data model question for

other Auditor
#1

Anyone that is using the Webi audit tables (log to database) successfully, I would like to hear how you have dealt with the relationship (or lack thereof) between the event log (OBJ_A_EVENT_LOG) and log detail (OBJ_A_EVENT_DETL) tables.

The event log (OBJ_A_EVENT_LOG) contains the session value and an event log type ID. The type ID tells you what sort of event took place (logon, get a list of corporate documents, refresh, and so on). Also in the event log table is the event ID (A_EVLOG_N_ID).

In the event detail table there are four columns. One is the Event ID, which would seem to join to the Event Log table. One is the Object ID, which is the ID of the element (document, universe) in the repository. The 3rd is the object name (document name, universe name, object name…) used in the event. And last is the object type ID, which tells you that “Resort” is an “Object” and “Island Resorts Marketing” is a “Universe”.

So here’s the problem… the Event ID is not unique across sessions. So if my session has an event “10” that is a login, and your session has an event “10” that is a “get list of corporate documents”, then there is no way to track which event details go with each session.

There is a listing in the Webi docs that shows which event detail types go with each event type. For example, a Login does not have any event details, so that would allow me to fix the problem example for event “10” mentioned above.

For example, the event “Compute Result” has details “Repository Name”, “Universe Name”, “Document Name”, “Object Name”, and “Number of Lines”. So I added a table to my universe that restricts the join between the Event Log table and the Event Detail table to show only valid details for each event.

Still not good enough… there are sessions where the event ID is not unique, and the event type has the same details. Again, an example… my session has an event “20” which is “Edit Document”. Your session has an event “20” which is “Refresh Document”. Now when I run a report, I see two document names attached to my event, and two document names attached to your event.

So I am forced to conclude that the audit tables - at least at the detail level - are not going to provide correct information. Either that or I am missing something obvious. I hope I am missing something. :slight_smile:

Can anyone that is using the audit tables shed some light on this? Feel free to email me off of the list if you like, and I will summarize and repost the results.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

#2

you…

In a message dated Mon, 30 Jul 2001 11:21:26 AM Eastern Daylight Time, DRathbun@AOL.COM writes:

<< Anyone that is using the Webi audit tables (log to database) successfully, I would like to hear how you have dealt with the relationship (or lack thereof) between the event log (OBJ_A_EVENT_LOG) and log detail (OBJ_A_EVENT_DETL) tables. >>

A followup: it seems as though at this installation they have used the audit tables for a Test cluster, followed by using the same tables for the Production cluster.

That means that the Event ID - while supposed to be unique - was not. Every duplicate Event ID that I found had a row from the test cluster that matched a row from the production cluster.

Which begs two questions…

  1. Why doesn’t BusObj create indexes on primary keys? This would have prevented this problem from the beginning.

  2. Why is the Event ID not in the repository Magic ID table? It appears from this “experiment” that the Event ID is a server based variable, not a Repository based variable. Thus, the duplicate values.

Anyway, I realize that the list is not a place to get answers for these two questions, but I am a bit frustrated (and a little embarrassed) at the amount of time it took me to figure out this problem. And it might help someone else down the line to figure out a similar problem.

Thanks to the person who suggested that I check if more than one cluster was using the same audit database. :slight_smile:

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

#3

u…

Dave,

From what I can tell, the EventID from the OBJ_A_EVENT_LOG table “should” be
unique but appears only unique to whatever server that is generating the log. I have a cluster that was migrated from an older server to a new one. The Event ID’s are each duplicated from the point that the server changed.

In an earlier version, we encountered a bug that caused the EventID to reset to 1 for every reboot that was performed on the cluster manager. This was later fixed in a service pack, and I am not sure of the version number where the Error occurs (I’m wanting to say 2.5.1/5.0.1 but I am not sure). If Host existed in the event details, I would suggest joining on host and event ID but it’s not there.

Looking at my Audit database, it appears that I’ve got corrupted data as well, as all of my Event ID’s are duplicating as of 6/22/01 (argh).

Blake

================================================== This message contains PRIVILEGED and CONFIDENTIAL information that is intended only for use by the named recipient. If you are not the named recipient, any disclosure, dissemination, or action based on the contents of this message is prohibited. In such case please notify us and destroy and delete all copies of this transmission. Thank you.

==================================================

Listserv Archives (BOB member since 2002-06-25)

#4

u…

In a message dated Wed, 1 Aug 2001 9:10:22 AM Eastern Daylight Time, “Sanders, Blake” Blake.Sanders@COMPUCREDIT.COM writes:

Dave,

From what I can tell, the EventID from the OBJ_A_EVENT_LOG table “should” be unique but appears only unique to whatever server that is generating the log.

I think it is by cluster rather than by server, but other than that I agree with you.

Looking at my Audit database, it appears that I’ve got corrupted data as well, as all of my Event ID’s are duplicating as of 6/22/01 (argh).

This is why I am considering adding appropriate primary key indexes on the audit tables… to prevent this sort of thing. Which is worse, data that is wrong? Or data that is wrong and you don’t know about it but use it to report to your CIO about your cluster statistics? :slight_smile:

Thanks for your feedback.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

#5

u…

“I think it is by cluster rather than by server, but other than that I agree with you.”

Oops, yes this is what I meant. SiteLog only runs on one machine in the cluster (typically the Cluster Manager).

Blake

================================================== This message contains PRIVILEGED and CONFIDENTIAL information that is intended only for use by the named recipient. If you are not the named recipient, any disclosure, dissemination, or action based on the contents of this message is prohibited. In such case please notify us and destroy and delete all copies of this transmission. Thank you.

==================================================

Listserv Archives (BOB member since 2002-06-25)

#6

Were you able to recover? Shaun and I will spend some time sifting through the data and perhaps using Oracle’s rowid assignment to try and fix the problems if there is a hope that we’ll be successful…

As to why, that was a rhetorical question right :wink: and ‘B’ on question 2…

Did you create primary keys? If so, what were they?

OBJ_A_EVENT_DETL.A_EVDET_N_ID
and
OBJ_A_EVENT_LOG.A_EVLOG_C_SESSION||A_EVLOG_N_ID

Cindy Clayton :us: (BOB member since 2002-06-11)

#7

In our situation, we had data from the test cluster included with data from our production cluster. In the header table, there is a host id, or something that let me identify the host system. From there I was able to determine if the record was from the test cluster or the production cluster. What I did from there was delete all records associated with any of the test cluster entries.

That meant that I deleted event details for production events, since the host id is not included at the event detail level. In other words, I cut out more data than was required. But since the keys were duplicated, there was really no easy way to handle it.

You could look at the event detail level and determine what values are valid in production and which are not, but it would be a huge chore, and probably not worth the effort.

Did you determine that you have this same problem?

Also, we did not create any keys (indexes) for fear that we would break something that Webi does behind the scenes while collecting data. I have in the past created indexes on repository tables, but never unique keys. You just never know what they’re doing with the data.

Good luck…

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#8

Ours was a little different. It was a result of a Webi reinstall. The event ids started over and caused duplication. Shaun and I looked at it and decided that we really couldn’t figure out any good way to put Humpty Dumpty back together again. The timing was bad for reasons I won’t get into here. We’re going to salvage the OBJ_A_EVENT_LOG for the records of logins, truncate the three data tables, and start over again.

Since I’m starting over I may play with creating the indexes and if I do I’ll let you know what happens.

Thanks a bunch! I owe ya!

Cindy Clayton :us: (BOB member since 2002-06-11)

#9

I’m looking for any suggested index’s for the Audit tables

Found this one:

CREATE INDEX OBJ_A_EVENT_LOG_N1 ON 
OBJ_A_EVENT_LOG(A_EVLOG_N_APPID) 
TABLESPACE WHATEVER_IDX PCTFREE 10 STORAGE(INITIAL 5242880 NEXT 524288 PCTINCREASE 0 ) 
; 

CREATE INDEX OBJ_A_EVENT_LOG_N2 ON 
OBJ_A_EVENT_LOG(A_EVLOG_N_TYPEID) 
TABLESPACE WHATEVER_IDX PCTFREE 10 STORAGE(INITIAL 5242880 NEXT 524288 PCTINCREASE 0 )

Any others?

Chris Pohl :us: (BOB member since 2002-06-18)