I have just created an ad-hoc report using Audit universe that came up with Installation CD. The results were not correct. I verified the joins between OBJ_A_EVENT_LOG and OBJ_A_EVENT_DETL tables and it showed many-to-many relationship which suppose to be one to many relationship as per the document.
The join is OBJ_A_EVENT_LOG.A_EVLOG_N_ID=OBJ_A_EVENT_DETL.A_EVDET_N_ID
Is that a bug? Any idea?
I would like to have the following info on the report.
User Name, Event Name , Event Start Time.
Try this version of audit. I have an outer join between those tables because not every event creates details. It also handles the session start and end with a database view. More discussion is here.
I used that universe and I am getting the same results. For an example, I refreshed a report ‘Report1’. I never touched and refreshed the report ‘Report2’ but it showed that I refresed that report many times. Then I found out that somebody refreshed the ‘Report2’ and the user-id and event-id got messed up.
It still shows many-to-many relationship when I checked the cardinality.
I manually flushed the cache from WebI admin tool. It looks like none of the tables have primary keys.
Do you have more than one cluster auditing to the same tables? Have you done a reinstall? Those will cause duplication. There is definitely a 1-N relationship between obj_a_event_log.a_evlog_n_id (1) and obj_a_event_detl.a_evdet_n_id (N). but you should never see duplication of the obj_a_event_log.a_evlog_n_id. If this query returns anything, you’re hosed!
select a_evlog_n_id, count(*)
from obj_a_event_log
group by a_evlog_n_id
having count(*) > 1
I’ve had duplication once due to a server upgrade without a purge of the tables. The upgrade started the numbers over again.
We recently upgraded our server from SQL Server 7x to 2000.
I checked the following tables and saw duplicate records and thats why it showed n to n relationship.
You can truncate the tables. You don’t have to delete and recreate them. I don’t recommend trying to put Humpty Dumpty together again once you’ve gotten duplication. Too complicated.
Cindy is right. Just truncate the tables and move on. We learned this the hard way too…
Auditor that is not well documented…each time you reinstall BO/Auditor, it resets the A_EVLOG_N_ID and A_STLOG_N_ID sequences since it stores it in the registry settings as LogSiteIndex and LogUserIndex if you’re using Windows or in boconfig.cfg if you’re using Unix. Next time if you reinstall be sure to change the registry values so that your ID’s are not reset back to 1 (and thus preventing duplicate records).
Although the whole doc doesn’t apply to your situation, ask Tech Support for their document named “Auditing Multiple WebIntelligence Clusters”. It describes where the ID’s are set. And if you ever move to multiple clusters, it provides an easy way to integrate all Auditor data.
Okay. I purged all the data from the tables and added master data to Application and Event name tables. I refreshed a report from WEBI and it stored all the events and looked perfect. I freshed a report through ZABO, but, it didn’t store all the information. I didn’t get the report name that I refreshed through ZABO. Is it only for WEBI?
I definitely saw Zabo refreshes. In fact, I saw too much data about a Zabo report retrieved from Corporate Docs. This post has useful info and read the entire thread to learn more. I never have seen Zabo refresh times. Those I can only get from Webi refreshes.
Yes…I saw the ZABO event logs in OBJ_A_EVENT_LOG table, but I didn’t see the report name which suppose to be stored in OBJ_A_EVENT_DETL table. It has WebI refreshed report name, but, not the ZABO refreshed report name. Any problem with master tables OBJ_A_APPLICATION, OBJ_A_EVENT_NAME?
Here is the data on these tables:
OBJ_A_APPLICATION has
1 Unknown
2 WEBINTELLIGENCE 2.7
3 BO Manager via WEBINTELLIGENCE
4 BUSINESSOBJECTS 5.0
5 BO Manager via BROADCAST AGENT
6 Zero Admin BUSINESSOBJECTS
7 WEBINTELLIGENCE 2.7
OBJ_A_EVENT_NAME has
1 Logon
2 Failed logon
3 Log off
4 Get list of inbox documents
5 Get list of corporate documents
6 Get list of universes
7 Send to users
8 Publish
9 Save
10 Read inbox document
11 Read corporate document
12 Read personal document
13 Selection of universe
14 Generation of list of values
15 Execution of query
16 Refresh of query from HTML
17 Login successful
18 Compute result
19 Refresh document
20 Compute result after drill
21 List of values
22 Edit document
23 Get document generated by reporter
24 Get a CSV document
25 Refresh document after a prompt
26 Reserved range limit
27 Get list of personal documents
28 Apply format
29 N/A
30 Open report from cache
31 Execute macro (VB)
32 Execute script (4.1)
33 Download agnostic document
34 BusinessObjects Zero Admin started
35 Delete inbox document
36 Delete corporate document
37 Delete personal document
38 Delete scheduled document
39 Upload agnostic document
Could some one verify with your data? I would appreciate it.
Yes, I do have your universes and reports.
I tried to execute your query, but I didn’t see any records in the table OBJ_A_OBJECT_TYPE. The query brought back some records since there was a outer join. Is OBJ_A_OBJECT_TYPE one of the master tables?
Here are the records in mine. I don’t remember how they were populated originally. Must have been done by turning on auditing. You’ll need other reference data as well.
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
1, 'Repository name');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
2, 'Universe name');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
3, 'Object name');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
4, 'User name');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
5, 'Group name');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
6, 'Number of lines');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
7, 'Number of elements');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
8, 'Document name');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
9, 'Document size');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
10, 'Browser IP');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
11, 'Browser OS');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
12, 'Browser name');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
13, 'Total size of documents in list');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
14, 'Description');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
15, 'Category Name');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
16, 'Keywords');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
17, 'Refresh Options');
INSERT INTO OBJ_A_OBJECT_TYPE ( A_OTYPE_N_ID, A_OTYPE_C_NAME ) VALUES (
18, 'Overwrite');
Thanks Cindy! I got the data from ZABO Admin guide and entered into a excel sheet and exported to the table.
Now, I am seeing all the ZABO activities. Another issue, When I refreshed the report thru WebI, it stored the report name with extension .rep, but not for ZABO reports. Any idea?
I noticed that too. In fact, if you look at my report I think I have a variable that strips off the .rep to make things consistent. I was also experimenting with writing a report of repository documents versus user created/owned documents and in order to do that, I was going to need to strip off the .rep for dp linking.
What does value 19 hold? Don’t know. I just did a scan, and that event detail type hasn’t occurred anywhere yet. Not sure if this is because we aren’t using a specific feature or if this is a future value or what…
If you’re populating this table manually, I just wanted to make sure you knew BO is populating a new value.
(BOB member since 2002-06-11)