BusinessObjects Board

Audit Tables

general discussion
#24

Cindy,

I downloaded your universe. When I try adn export it, I get the error: “Could not Export Universe”. I have the valid connections and everything. Is there some setting in the universe which I need to change?

BO_User (BOB member since 2002-10-02)

#25

No clue :confused: . Did you try searching on that error message?

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

#26

I refreshed a report through Webi and it took around 12 mins (720 seconds).
I generated a report from Auditor universe for getting the “Actions” performed.

It is giving me around 5000 seconds as the sum of action time for all the actions !!!

The actions and time are : -

  1. Refresh Document - 2100 seconds
  2. Compute Results - 2289 seconds
  3. Get document generated by reporter - Empty

Can someone please explain this?

Regards,
Neeraj.

neeraj.monga (BOB member since 2003-12-09)

#27

Neeraj, have you restarted the WebI server? Look at this earlier post in this thread where Cindy mentions the possible need to clear out the audit tables to avoid duplicates…

You may have the same Audit ids from the past that have been reused, so a report makes the totals look inflated?

Anita Craig :us: (BOB member since 2002-06-17)

#28

Anita,

Thanks for the response.

Need some more help in this regard.

This is a sample of the Report generated from BO Repository. Here, what should be the “Total Time taken by the Report”? Sum of all the actions or Max (Action Duration) ?

Also, is it advisable to use Action Name with Action Type?

Regards,
Neeraj.

neeraj.monga (BOB member since 2003-12-09)

#29

See if this from BOB’s Downloads is helpful.

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

#30

Is it possible to let this utility also work with AS/400?

Regards,
Wim

Wimhaze (BOB member since 2004-09-17)

AS/400
#31

Cindy, I downloaded the Auditor sample unv and reports from the BOB download area. I’m new to BO so bare with me if my issues are elementary. I created the additional view successfully by running the sql script provided. I was also able to export the unv, but when doing a check there were several errors as listed below:

As I said, I’m new to BO and SQL for that matter. I am using BO 6.5.1 with the Audit tables residing on a SQL 2000 db. When I try to run one of the reports in full client I get an illegal outerjoin error also. Was this designed for an Oracle db? I’m pretty sure that is why the “instr” and “concat” commands are erroring? Thanks for any help or insight.

brewdude (BOB member since 2004-09-21)

#32

I’m sending auditing info to SQL 2000 and would like to convert the UNV posted by Cindy Clayton from Oracle specific to MSQL. Is there anyone out there that could convert these two statements for me?

decode(instr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,'.'),0,OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,substr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,1,instr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,'.')-1))

and

count(CONCAT(@Select(Session details\BusinessObjects UserID),OBJ_A_EVENT_LOG.A_EVLOG_C_SESSION))

MSQL doesn’t understand “decode” and I’m sure this would need to be converted to a “case” type statement, and it complains about the “concat” statement. I’d obviously do this myself, but I’m not very experienced with SQL. Thanks for any help.

brewdude (BOB member since 2004-09-21)

#33

another option is to decipher what the decode does by looking at the actual table columns to decide if you even need this. I have a very simple audit universe that works quite fine and probably does 80% of what Cindy’s does. Further manipulation can be done in Reporter until you get a sense of the data and learn more about BO. Try a simple list of the data to look at and analyze. Once you have a handle on the data, then go play with the universe. No doubt Cindy started this way building her version.

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

#34

You are right, I am trying to make this Unv exactly the same, which it may not need to be. Thanks for the suggestion.

brewdude (BOB member since 2004-09-21)

#35

You wouldn’t consider sharing the audit universe you’ve setup with me would you?

brewdude (BOB member since 2004-09-21)

#36 
decode(instr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,'.'),0,OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,substr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,1,instr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,'.')-1))

This strips .rep off the end of a report name. I found that sometimes I saw ReportName.rep and at other times ReportName. Using that object returns equal report names in both cases. Technically what it is doing is starting at the beginning of the report name and searching for a ‘.’. If it doesn’t find one it returns EVDET_C_OBJNAME. If it finds one, it starts substringing at the beginning and returns to the ‘.’ minus 1. Make sense? You may need that one.

count(CONCAT(@Select(Session details\BusinessObjects UserID),OBJ_A_EVENT_LOG.A_EVLOG_C_SESSION))

Not sure about this one. The universe I started with was from BO so it is probably something that was already in there. To ‘decode’ it for you, it is concatenating the session user and sessionid and counting the number of those it finds.

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

#37

This is all moot now. After some investigation and prodding of BO it was found that we were intitled to Auditor with the package we purchased. Thanks anyway.

brewdude (BOB member since 2004-09-21)

#38

We have a universe created for SQL Server if anyone else is interested.

dnewton :us: (BOB member since 2004-01-30)

#39

You can send it to bobdownloads@forumtopics.com if you don’t mind. That will share it with everyone.

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

#40

I am trying to run a report which shows “who has access to which universes”. The problem is that it gets me the permissions set at the folder inherited, not at the user level itself.

So, if you have disabled certain universes at the user level which are enabled at the inherent folder level, you will still get a 1 for Universe Permission. :expressionless:

Any thoughts on how to fix this?

Thanks

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#41

I would definitely be interested. Can you upload it to BOB?

bregent (BOB member since 2004-02-06)

#42

Sorry, yes, did this a while ago - it’s at:

dnewton :us: (BOB member since 2004-01-30)

#43

Hi,

I am using a report to know who is using which report. The query is

       SELECT

decode(instr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,’.’),0,OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,substr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,1,instr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,’.’)-1)),
OBJ_A_OBJECT_TYPE.A_OTYPE_C_NAME,
OBJ_A_EVENT_LOG.A_EVLOG_C_USER,
OBJ_A_EVENT_NAME.A_EVNAM_C_NAME,
OBJ_A_APPLICATION.A_APPLI_C_NAME,
COUNT(OBJ_A_EVENT_LOG.A_EVLOG_N_TYPEID)
FROM
OBJ_A_EVENT_DETL,
OBJ_A_OBJECT_TYPE,
OBJ_A_EVENT_LOG,
OBJ_A_EVENT_NAME,
OBJ_A_APPLICATION
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(+) )
AND ( OBJ_A_EVENT_LOG.A_EVLOG_N_APPID=OBJ_A_APPLICATION.A_APPLI_N_ID )
AND (
( OBJ_A_EVENT_LOG.A_EVLOG_N_TYPEID IN (‘19’,‘25’)
AND OBJ_A_EVENT_DETL.A_EVDET_N_OBJTYPID = ‘8’ )
)
GROUP BY
decode(instr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,’.’),0,OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,substr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,1,instr(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,’.’)-1)),
OBJ_A_OBJECT_TYPE.A_OTYPE_C_NAME,
OBJ_A_EVENT_LOG.A_EVLOG_C_USER,
OBJ_A_EVENT_NAME.A_EVNAM_C_NAME,
OBJ_A_APPLICATION.A_APPLI_C_NAME

It is working fine . I get the data from 2002. If i need data from 2004, What are the modifications to be done above. Please advice.

Thanks in advance
Regards
Suamnth

sumanth (BOB member since 2004-09-24)

Audit report