Audit Tables

system · October 12, 2004, 4:35pm

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

brewdude (BOB member since 2004-09-21)

system · October 12, 2004, 5:24pm

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 (BOB member since 2002-06-11)

system · October 13, 2004, 7:39pm

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)

system · October 14, 2004, 9:22pm

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

dnewton (BOB member since 2004-01-30)

system · October 15, 2004, 12:52am

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

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

system · November 29, 2004, 7:30pm

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.

Any thoughts on how to fix this?

Thanks

Kashif

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

system · December 7, 2004, 6:44pm

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

bregent (BOB member since 2004-02-06)

system · December 7, 2004, 7:02pm

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

dnewton (BOB member since 2004-01-30)

system · April 21, 2005, 6:55am

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)

system · April 21, 2005, 4:40pm

It doesn’t look like it’s doing any filtering by year.
Are you sure there’s 2004 data on your audit tables?

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

system · April 22, 2005, 5:32am

yes. I get data from 2002 to till now with that query. But i ned to view data only from 2004 and specific month. Can we get it

Thanks

sumanth (BOB member since 2004-09-24)

system · April 22, 2005, 1:21pm

Try adding something like this to your where clause:

  AND  DATE(OBJ_A_EVENT_LOG.A_EVLOG_D_STARTIME)  >=  @variable('1) Start Date')
  AND  DATE(OBJ_A_EVENT_LOG.A_EVLOG_D_STARTIME)  <=  @variable('2) End Date')

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

SebastienGoiffon · April 22, 2005, 8:57pm

Hi Chris,

I didn’t understand first what he wants like you

The answer is on the other post

Regards

system · April 25, 2005, 3:03am

Yep, I noticed we came up with the same solution

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

system · December 15, 2005, 2:10pm

I am trying to fine tune the data we are collecting with auditing. I am really only interested in who/ which/ when a report was run and am getting that data. The problem is that, by default, I am also getting audit information on each object that is contained in the report. This is resulting in 100+ rows being generated, in the audit tables, for each report that is refreshed. Is there a way to turn this part of the audit off?

bsastry (BOB member since 2004-04-29)

system · December 15, 2005, 2:26pm

I don’t think so. The way we handle it is first by choosing the log to text file option. We then have a daily scheduled job that processes the text file and loads to a database only those pieces of information that we want to retain.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · December 15, 2005, 3:25pm

I think you’re selecting too many columns, thus forcing it to give you back more data than you need, here’s the SQL we use to do similar (ignore the ALL prompts below, that’s a different tip)

SELECT
  OBJ_A_EVENT_LOG.A_EVLOG_C_USER,
  OBJ_A_APPLICATION.A_APPLI_C_NAME,
  COUNT(OBJ_A_EVENT_LOG.A_EVLOG_N_TYPEID),
  OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,
  OBJ_A_EVENT_LOG.A_EVLOG_D_STARTIME
FROM
  OBJ_A_EVENT_LOG LEFT OUTER JOIN OBJ_A_EVENT_DETL ON OBJ_A_EVENT_LOG.A_EVLOG_N_ID=OBJ_A_EVENT_DETL.A_EVDET_N_ID,
  OBJ_A_APPLICATION
WHERE
 (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  )
  AND  DATE(OBJ_A_EVENT_LOG.A_EVLOG_D_STARTIME)  >=  @variable('1) Start Date')
  AND  DATE(OBJ_A_EVENT_LOG.A_EVLOG_D_STARTIME)  <=  @variable('2) End Date')
  AND  ( (( LEFT(OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME,3) ) IN @Prompt('Enter Application(s) (ALL for all)','A','Event Detail\Application',multi,free) OR 
'ALL' IN @Prompt('Enter Application(s) (ALL for all)','A','Event Detail\Application',multi,free) OR 
'All' IN @Prompt('Enter Application(s) (ALL for all)','A','Event Detail\Application',multi,free) OR 
'all' IN @Prompt('Enter Application(s) (ALL for all)','A','Event Detail\Application',multi,free) OR 
'*' IN @Prompt('Enter Application(s) (ALL for all)','A','Event Detail\Application',multi,free) OR 
'(ALL)' IN @Prompt('Enter Application(s) (ALL for all)','A','Event Detail\Application',multi,free) OR 
'%' IN @Prompt('Enter Application(s) (ALL for all)','A','Event Detail\Application',multi,free))  )
  )
GROUP BY
  OBJ_A_EVENT_LOG.A_EVLOG_C_USER, 
  OBJ_A_APPLICATION.A_APPLI_C_NAME, 
  OBJ_A_EVENT_DETL.A_EVDET_C_OBJNAME, 
  OBJ_A_EVENT_LOG.A_EVLOG_D_STARTIME
WITH UR

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

system · January 30, 2006, 12:11pm

Hi,

Can anyone please let me know what OBJ_M_DOCUMENTS.M_DOC_N_TYPE is!!! I have downloaded the SQLserver uni, and its working great other than i cant see info from the BCA console due to the self restricting OBJ_M_DOCUMENTS.M_DOC_N_TYPE=1 join… any ideas on what this is? -I am, reluctant to remove it without knowing why it was/is there!

Thanks in advance…

Steve

snakeboy (BOB member since 2003-12-02)

system · January 30, 2006, 12:15pm

Hi Steve,

here is the list of all values possible in M_DOC_N_TYPE column:

BODT_UNDEFINED = 0
BODT_DOCUMENT = 1
BODT_SCRIPT = 2
BODT_DOCUMENTBATCH = 4
BODT_LOV = 8
BODT_TEMPLATE = 16
BODT_BQY = 32
BODT_COMPILEDSCRIPT = 64
BODT_WEBDOC = 128
BODT_ADDIN= 256

So in your case it is BO full-client document.

Marek Chladny (BOB member since 2003-11-27)

system · January 30, 2006, 1:12pm

Brilliant thanks Marek,

Tho the type for the docs returned without this self restruicting is of type 4 = BODT_DOCUMENTBATCH - any idea what these are!!! - I thought that they were normal FC reports sent through BCA…

Confused!!!

Thanks again,

Steve

snakeboy (BOB member since 2003-12-02)