Audit Tables

system · August 5, 2002, 7:29pm

I put this topic on my “watch list” so I can come back to it. I’ll try to get some info to post… right now I’m in the middle of doing a training class. And I have a few things to say about Auditor…

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

system · August 8, 2002, 3:08pm

OK y’all! I’m having a terrible time with understanding how the audit tables work. Does anyone have anything I can read besides the manual and list posts?

I turned on auditing to a database. That’s working fine from what I can see. I truncate obj_a_event_detl, obj_a_event_log, and obj_a_site_log. I run my report. No data! So far so good.

I wait a while to make sure that everything SHOULD be in the database. The delay is set to 90 seconds.

Then at 10:57 I log in, hit corp docs, hit report link, report autorefreshes, I fill in prompts, I hit run query, I see data via Adobe, I hit log out.

I get the 10:57 records but also some stray ones from 10:46. Happens EVERY single time.

Is it just me?

Based on additional testing, it seems like some stuff just kind of hangs in the audit cache till a subsequent event…wakes it up so to speak… Anyone else experiencing this?

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

system · August 9, 2002, 6:45pm

I’m tearing my hair out trying to interpret the audit data I’m receiving for Zabo events. I think I’ve figured out that there is a relationship between the number of data providers in the document and the number of refresh events in the audit tables. I’ve got a document with ONE data provider (no prompts) and I would have expected only ONE refresh event. Instead I have this…

EventId         EventTraceTypeName           EventTraceName
5403            Document Name                Application Permissions
5403            Number of Lines              162
5403            Universe Name                RepoREPD
5404            Document Name                Application Permissions
5404            Number of Lines              162
5404            Universe Name                RepoREPD

See how I’ve got two sets of event info… The ONE data provider did pull 162 records. Also, the durations are all showing up as 1 millisecond. Am I not supposed to be able to tell how long a Zabo query ran?

I’m LOVIN this forum! How cool to be able to show stuff in a browser and be pretty sure the person looking sees the same thing. No more little ASCII drawings. Now if BOB would only let us upload screen shots

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

system · August 9, 2002, 6:51pm

There is no creative power in whining!

Regarding the ZABO auditing… I don’t recall if the query duration is captured for ZABO or not. I think it should be. Are you sure you have the joins configured? You’re not just duplicating rows because of a missing join clause or something? How about posting the SQL used to get that data…

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

system · August 9, 2002, 7:46pm

And in between all the forum work he STILL finds time to respond to my plea for help!

I’m as sure that the joins are right as BO was when they created the canned audit universe that I’m using to make the report! Here’s the sql that was generated…

SELECT
  OBJ_A_EVENT_LOG.A_EVLOG_C_SESSION,
  OBJ_A_EVENT_LOG.A_EVLOG_C_USER,
  OBJ_A_EVENT_LOG.A_EVLOG_D_STARTIME,
  OBJ_A_EVENT_LOG.A_EVLOG_N_DURATION,
  OBJ_A_EVENT_NAME.A_EVNAM_C_NAME,
  OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,
  OBJ_A_EVENT_LOG.A_EVLOG_N_ID,
  OBJ_A_EVENT_LOG.A_EVLOG_N_TYPEID,
  OBJ_A_EVENT_LOG.A_EVLOG_N_DURATION/60000,
  OBJ_A_OBJECT_TYPE.A_OTYPE_C_NAME
FROM
  OBJ_A_EVENT_LOG,
  OBJ_A_EVENT_NAME,
  OBJ_A_EVENT_DETL,
  OBJ_A_OBJECT_TYPE
WHERE
  ( OBJ_A_EVENT_LOG.A_EVLOG_N_ID=OBJ_A_EVENT_DETL.A_EVDET_N_ID  )
  AND  ( OBJ_A_EVENT_LOG.A_EVLOG_N_TYPEID=OBJ_A_EVENT_NAME.A_EVNAM_N_ID  )
  AND  ( OBJ_A_EVENT_DETL.A_EVDET_N_OBJTYPID=OBJ_A_OBJECT_TYPE.A_OTYPE_N_ID  )

It hurts to watch the head banger for too long!

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

system · August 13, 2002, 5:14pm

A bit more info. It appears that THIS is what is happening…

I log into Zabo and run a report. I get two rows of data for each data provider. Based on the number of rows I see I have determined that ONE row is for the report refresh and the other is dumping out the stored last run info that it typically held in the repository. For example:

I ran a report and exported. I then started a new ZABO session and grabbed the report. I then refreshed (using prompt values to get DIFFERENT results than would have been stored from the run just prior to the document export). I got two rows for each provider and I saw 40 rows and 129 rows. In my data manager the current run had 129 rows and the one just prior (that would have been stored in the obj_m tables), had 40 rows. Unfortunately, I can’t see any way to separate the previous run and current run except that the time stamp on the previous one was just a tad bit earlier than the refresh (within a minute or so of each other). I also can’t see a way to get the names of the data providers (a problem in a document with 8 providers DUPLICATED). I also have yet to see a duration other than ‘1’ (not -1 but 1) for any of my Zabo refreshes.

Thoughts?

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

system · August 13, 2002, 5:22pm

A “Monthly Sales” report accessed via Webi will have an event trace name of “Monthly Sales.rep”. The same report accessed via Zabo will have “Monthly Sales” as an event trace name. The .rep appears for the Webi refresh but not the Zabo one.

A login first shows up as user = unknown. Once the user actually DOES something (like list corporate docs), the real userid is displayed.

Ive never seen a webi logout, despite the fact that Ive always used the logoff button

Update 8/14/02 - Certain events (logoff, ZABO launch, login success) don’t have obj_a_event_detl records. If you want to see a session from beginning to end, outer join from obj_a_event_log to obj_a_event_detl. In the canned universe you can also outer join from detl to obj_a_object_type if you want to include the Event Trace Type Name in your query)

Not all the records dump from the cache reliably. Ive waited much longer than the 90 second cache flush setting, but records wouldnt dump till I inserted more by logging in again. This seems to happen with every session…that some stray records remain in the cache.

If you hit edit from webi and zabo launches, a new session id is assigned. I’ve never seen mixed Webi and Zabo actions within the same session id.

I see no durations other than 1 for zabo refreshes

When my full client document is processed via webi, I see one row for the report refresh with a duration that seems to be the sum of the duration of all the queries and a number of rows that equals the total rows for all the queries in the document.

Audit records for Zabo refreshes are very confusing. Look here for more info.

As always, I’m more than happy to be corrected.

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

system · August 13, 2002, 6:23pm

Correct. Until the user logs in, the user name is unknown. The fact that they log the attempt to log in means you can log hackers trying to get into your system.

I’ll have to check our records; I think that I have seen logout entries.

Also experienced this. In one case we log the audit records to a file and load the file ourselves; we get more reliable results that way.

This is also by design. Since a Zabo launch from Webi runs two WIQT’s, you get two audit entries. If you launch Zabo from the icon on your desktop, you only get one.

Cindy, you don’t by chance have more than one webi server, do you? We had some really strange results like this when we had (by mistake) our production webi cluster and a test webi cluster logging to the same database.

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

system · August 13, 2002, 6:36pm

Nope nope nope. I’m only auditing one webi server. I’ve never seen anything but the dev server (webdev5) listed as the event host. I repeated the test enough times…running the report…getting ‘X’ number of rows…exporting…running again to get a different ‘X’ number of rows…seeing two records for each query (one for the saved last results and the other for the current refresh). Just wanted to know if others see the same thing.

At this point I’m only going to count on Zabo to provide me with info on WHAT documents are processed. I’ll get the durations from Webi refreshes.

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

system · August 14, 2002, 5:33pm

Updated Audit Observations to include info on why I wasn’t able to see logoff events.

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

system · August 20, 2002, 10:45am

Hi.

I would like to add something from my experience with the Audit tables.

The field OBJ_A_EVENT_LOG.A_EVLOG_N_ID was reseted by the beginning of the year. This caused a big mess with the events having the same number in different years.
After some updates on WebI the field OBJ_A_EVENT_LOG.A_EVLOG_N_ID also had strange numbers there.

To solve this problem and stabilize the environment I added a primary key to the table with the OBJ_A_EVENT_LOG.A_EVLOG_N_ID field. Since then it is working fine.

nxa (BOB member since 2002-08-19)

system · August 20, 2002, 12:32pm

Thanks so much for adding your observations! These forums are going to be great!

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

system · August 20, 2002, 5:22pm

I didn’t see the view in a previous post. Is it listed on the web site?

Rana Kassel (BOB member since 2002-06-21)

system · August 20, 2002, 10:39pm

He posted the view info on busob-l. I found it there. Basically it selects sessionid, min(datetime), max(datetime) from obj_a_event_log and groups by sessionid. Wait a minute…let me look it up

OK. Here it is…

select a_evlog_c_session, min(a_evlog_d_startime) session_start,
max(a_evlog_d_startime) session_end
from obj_a_event_log
group by a_evlog_c_session

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

system · January 13, 2003, 2:57pm

I haven’t gone thru each one of these scenario, but it will be worthwhile to read the Web Intelligence System Admin manual. Check the section ‘what is being audited’ and you will know that everything is not being audited for ZABO session as compared to WebI session.

dcdas (BOB member since 2002-06-17)

system · January 28, 2003, 8:46pm

I’m having trouble with my audit database data. I’ve searched and researched. I see that a particular user ran a report. It ain’t possible. He is one of my super secure users. He doesn’t even have access to the domain that the report he supposedly ran lives in. I remember a post from the archives where someone else saw this problem but I can’t find it at the moment.

We are only auditing one cluster. The cluster contains a manager and a node used for BCA processing (but the node does sometimes run busobj.exe to serve users processing full client reports over the web).

Thoughts?

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

system · January 28, 2003, 9:47pm

I figured it out. My duplication started around Christmas day. A present from Santa perhaps? Nope, an upgrade to 2.7.2 .

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

system · February 13, 2003, 1:39pm

If you’ve seen durations on Zabo events in the audit database I’d virtually pay to hear from you . I’d also love to hear from anyone using the Auditor product who gets durations on Zabo queries. I haven’t seen a darned thing at either of two client sites!

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

system · February 13, 2003, 4:18pm

looking thru the doumentation for object type ID and event ID, there is nothing there for duration. The closest thing I see is called “number of lines” ==> object type ID = 6 and I have never been really sure about that one and what it means.

Seems like to get a query duration, you need to look your the RDMS log. Our DBA was able to pull this data from the DB2 log and populate a summary table every night. I use this with audit report to see what a users does but it doesn’t actually show me the duration of a query, just user summaries by connection, plan, ID, date. If I asked I could probably get more detail; it’s all there.

scott copeland (BOB member since 2002-08-15)

system · February 13, 2003, 5:36pm

I get query durations fine for queries processed by app names ‘BOManager for Webi’. The only durations I don’t get are the ones for app name ’ BusinessObject ZA’. The durations for ‘BOManager for Webi’ show up in ‘Refresh Document After A Prompt’ and ‘Refresh Document’.

Part of the premise of Auditor is that you can report on Zabo activity and I’m shocked that query duration isn’t an essential part of that info. Surely someone has Zabo query durations .

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