view audit reports..

[Former Member]

hi,

I have few queries in regards to Audit reports..Before that my environment is

Unix SOlaris 10.2 (clustered with another box so 2Unix containers / Oracle 10.2 / BO XI 3.1)

1. I have enabled Auditing on both of the boxes but how do i figure it out if the auditing is acutally enabled.

I did enable Audit features for the servers that i want to be audited but still how to confirm this ?

2. Do I need enable auditing by going under each Crystal report under Audit folder and tick the box ?

3. I loaded ORACLE_AUDIT_BIAR file on server and then loaded the AUDIT_EN_BIAR files > this created an AUDIT folder and I can view all sections under it.

4. Will change the parameter for Acitivity Universe to point to my ORACLE DB... But i guess in doing so the option of DefaultBOAuditingDB will go away and get replaced by my ORACLEDB ?

5.How do I get more information on the metadata Schema of CMS (SI_infoobjects6 etc..) I tried to lookup and find any documentation which would say detail descriptions on what each table means but in vain.?

6. I just need 3 main Audit reports >>

a. Find the START TIme of the Report when Run and End TIME of REport when it finished populating records.

b. Find the number of Lines populated in the report after it has run ?

c. FInd the size of the report after it has run ?

How to go about just these 3 reports ?

please advise,

regards

[Former Member]

Thanks Manu / Rahul for your thoughts. But honestly I guess that does not suffice in my present condition.

We view reports through websphere API, so a customised webpage where users log on and run reports.

I was wondering in that case what EVENT type do they get captured as certainly they are not DOCUMENT REFRESHED, I tried OBJECT CREATED / Report Created for "CRYSTAL REPORTS" but that did not return any values for NUMBER OR LINES in a REPORT or even values under DURATION FIELD as I guess DURATION FIELD get populated for DESKI reports only ? and if you run reports through INFOVIEW ?

So yea Rahul - You suggested the timestamp field. But this field only  displays in dd/mm/yyyy format and NOT the TIME.

DURATION field for some event is displayed as 1 and in most cases its 0 or NULL. SO what does this 1 means ?

IF you could provide ans with respect to webshpere based architecture ? and its BO XI 3.1 SP3 on UNIX box > with ORACLE 11g as DB.

Regards

[former_member184512]

Hi,

work around for date and time issue in 4.0,

Write click on "event start time" dimension and selct edit date format, at the bottom you should be able to see one format where it represents in AM and PM (ex: 04/22/2012 10:45:56 PM), select it and save it.

Now you can see the date and time on webi, crystal reports. Not sure about 3.1 sp3. you can try it.

[former_member184512]

Hi,

I have few updates on your question.

Let me explain about my information.

we are on 4.0 SP2 and Oracle DB and we have all BICS adhoc reports.

1. Through oracle db we cant find the report execution time as simple as by considering start time and end time. We need to consider view, refresh, prompt, retrieve events to get approximate time of report execution time. But we have too may bugs in those.  ADS not able to capture correct event time like we see view, retrieve, prompts Event Duration as "0". Even SAP able replicate the issue on their end. They are in the process of analysis with Development team.

2. We see event duration for refresh but it shows as MS but in reality it should be Seconds. SAP confirmed it as bug, I am waiting for ADPT number.

3. Event Start time works perfectly in IDT but when I use the same dimension in webi reports it gives wrong data, SAP able to replicate the same issue on their end. waiting for ADPT number.

finally i can clear say that we can not get the report execution time if we have BICS reports as ADHOC based, but we can get the report execution time only if reports are sechule. Run event gives an complete time.

I went through a hard time to make sap to replicate the issues on there end. Now SAP accepted that they see same issues with few other clients too.

[Henry_Banks]

Hi,

I would be interested to know more. 

Please quote your OSS  and any ADAPT you may be tracking for this issue specifically.

Regards,

H

[former_member249128]

Hi Priyank,

To verify if the Auditing has been enabled on both the servers or not, you'll have to check the properties of SIA on both the machines. Execute serverconfig.sh for this on both machines.

To verify what all events are getting audited, it has to be checked under "Audit Events" section on each server properties in CMC.

I am not sure what do you mean by this - "Will change the parameter for Activity Universe to point to my ORACLE DB... But i guess in doing so the option of DefaultBOAuditingDB will go away and get replaced by my ORACLEDB ?"

If you have Oracle as your auditing db server, import the 'Activity' universe designed for Oracle DB from the 'Samples' directory and point it your Oracle Database.

You'll also have to import the auditing reports, which I guess you already did.

There is no way from which you can retrieve the metadata schema of the tables that you listed as these are virtual tables which can be read and written to by CMS only.

For the three reports only, import the auditing reports from samples biar and delete all those reports which are not necessary or else you can create your own reports against the Activity universe.

I hope this will help you.

Regards,

Rahul

[Former Member]

Hi,

Can we also track on Report Run times and/or Peak load usage / ?

Thats what we are after to find out on what the activity is like in CMS and find out what will be peak times when reports are run and which reports? along with The report run time from Start to Finish populating the records ?

This is getting quite critical for us as we need to goto the bottom of it to avoid bottlenecks in prod system when it goes live.

So effectively we are just after 3 things

1. Start and End time of report run

2. Peak usage time for reports that have run.

3. Which reports have run the max no. of times.

Please advise,

regards

[Former Member]

Hi,

Just wondering if anyone has any ans to this ? or have populated such reports at their work place ?

Please advise,

Regards

[Former Member]

Hi Priyank!

For the reports runs there is already a report within the Audit package provided by a biar. Look into this installation folder (Business Objects\BusinessObjects Enterprise 12.0\Samples)

Point 1 and 2 can only be reported if you enabled the points in the CMC for the applications.

For the CMS tables, I think you shouldn't bother with them. There is query to decode the encoded Names, but there are still many items left open.

I would rather stick to the AdminQuery tool, which will give you an overall information about any object in the CMS you like. There is also a Java binary which can be invoked by the command line. And with the same query you can also use the BIAR binary to import/export data from the CMS.

And for the details about the AdminQueries, well that is also not well documented. There is/was an article about it, but I think it only covers the smallest portion. Perhaps the Wiki pages for BO have more information about it. If you need anything in detail about this, I could post my wiki pages.

ciao Hakan

[Former Member]

Hi Hakan,

Thanks for the reply back. Really appreciate it.

Well, I have enabled all Audit Event under CMS / RAS / All Crystal servers instances under CMC / Adaptive Job Servers etc etc...

I am still after 2 main reports :-

1. Report Start and End times (From this I can calculate how long did it take for a report to finish its run).

2. No. of Lines from a report or a report size(no. of records after its has completed its run).

I tried to look for fields under AUDIT_EVENT but could not find any information, thought DETAIL_TYPE has a field value of 9 - No. Of lines, however nothing ever shows that type of records in the AUDIT DB.

So yea, If you can send me any examples of codes used or how to decrypt the data from InfoObjects6 etc.. then that would be great. just great.

Please advise and post any relevant documents / wiki for me to have a study on it.

Regards

[Former Member]

Hi Priyank!

Here a sample statement - which should be generated by the Audit universe

SELECT

  DETAIL_TYPE.Detail_Type_Description,

  AUDIT_EVENT.Start_Timestamp,

  AUDIT_EVENT.Duration,

  AUDIT_EVENT.User_Name,

  DBMS_LOB.SUBSTR (AUDIT_DETAIL.Detail_Text, 1000, 1),

  AUDIT_EVENT.Event_ID

FROM

  DETAIL_TYPE INNER JOIN AUDIT_DETAIL ON (DETAIL_TYPE.Detail_Type_ID=AUDIT_DETAIL.Detail_Type_ID)

   INNER JOIN AUDIT_EVENT ON (AUDIT_DETAIL.Event_ID=AUDIT_EVENT.EVENT_ID and AUDIT_DETAIL.Server_CUID=AUDIT_EVENT.SERVER_CUID)

   INNER JOIN EVENT_TYPE ON (AUDIT_EVENT.Event_Type_ID=EVENT_TYPE.Event_Type_ID)

   INNER JOIN V_DATE_HIERARCHY_PLUS  AuditEventDate ON (trunc(AUDIT_EVENT.START_TIMESTAMP)=AuditEventDate.DATE_FROM)

WHERE

  (

  EVENT_TYPE.Event_Type_Description  IN  ('Document Refreshed')

  AND  AuditEventDate.DIFF_DAYS  BETWEEN  -7 AND -2

  )

If you filter for "Document Refreshed" you will find that the column DETAIL_TYPE.Detail_Type_Description, will have "Number of lines" as type and DBMS_LOB.SUBSTR (AUDIT_DETAIL.Detail_Text, 1000, 1) as value will give you the number of lines found.

This column AUDIT_EVENT.Duration will give you the total time until the document was refreshed in seconds.

In trying to produce a test for you, I found that my Audit universe is not 100% correct.

So for the decode I posted this on G+ - is a function which almost succeeds to decode the repository entries.

Here the link https://plus.google.com/b/111882487149326110835/

ciao Hakan

[Former Member]

Hi Hakan,

Thanks for the reply back.

I was supposed to respond to you yesterday but just couldnt get time.

Well in your query that was passed onto me I can see a mention of AUDITEVENDATE table ? hmmm this table unfortunately does not exist in our AUDIT Database and I am sure AUDIT DB gets generated automatically.

So when i tried to run the query it threw me an error on invalid table name.

So can you please advise on that.

In regards to your query posted on google+ page, I am supposed to run that query on Querybuilder of AdminTools of cMS?

I actually noticed that my AUDIT_EVENT.DURATION field is not populating any records, so does that mean AUDIT Is not getting captured ?

We do not use Infoview for scheduling or viewing reports but we use a Websphere base web application to view and run reports.

Please advise,

[Former Member]

Hi Priyank!

Sorry, you really don't need this table here the statement without

SELECT

  DETAIL_TYPE.Detail_Type_Description,

  AUDIT_EVENT.Start_Timestamp,

  AUDIT_EVENT.Duration,

  AUDIT_EVENT.User_Name,

  DBMS_LOB.SUBSTR (AUDIT_DETAIL.Detail_Text, 1000, 1),

  AUDIT_EVENT.Event_ID

FROM

  DETAIL_TYPE INNER JOIN AUDIT_DETAIL ON (DETAIL_TYPE.Detail_Type_ID=AUDIT_DETAIL.Detail_Type_ID)

   INNER JOIN AUDIT_EVENT ON (AUDIT_DETAIL.Event_ID=AUDIT_EVENT.EVENT_ID and AUDIT_DETAIL.Server_CUID=AUDIT_EVENT.SERVER_CUID)

   INNER JOIN EVENT_TYPE ON (AUDIT_EVENT.Event_Type_ID=EVENT_TYPE.Event_Type_ID)

WHERE

  (

  EVENT_TYPE.Event_Type_Description  IN  ('Document Refreshed')

  AND  trunc(AUDIT_EVENT.START_TIMESTAMP)  > trunc(sysdate)-7

  )

The AUDIT_EVENT.DURATION is only filled when the document is refreshed. If you don't have any "Document Refreshed" entries in Event_Type, you may look into the CMC - Application setting, there the audit can be enabled for all or for document refreshes.

Please check which report type you use and change in Application the corresponding one "Desktop Intelligence" or "Web Intelligence"

ciao Hakan

[Former Member]

Hi Hakan,

I tried running your code finally, but I guess I didnt tell you before on how do we view reports.

We view reports through websphere API, so a customised webpage where users log on and run reports.

I was wondering in that case what EVENT type do they get captured as certainly they are not DOCMUMNET REFRESHED, I tired OBJECT CREATED but that did not return any values for NUMBER OR LINES in a REPORT or even values under DURATION FIELD as I guess DURATION FIELD get populated for DESKI reports only ? and if you run reports through INFOVIEW ?

CAn you please advise from your experience on how to get around with this and how should i reach to my 2 goals ?

1. REPORT SIZE

2. REPORT DURATION (how long it took to run a report?)

Regards

[Former Member]

Hi,

Can anyone ponder any thoughts on the above query please ?

Thanks and regards

[former_member249128]

Hi Priyank,

AUDIT_EVENT.DETAIL_TYPE_ID = 51 gives you information if the report content being viewed is 'Live' data or 'Saved' data.

AUDIT_EVENT.START_TIMESTAMP for the event typeid 199609 gives you the information when the report was viewed (it is time when the report displays data, not when you click on 'View') and Duration gives you information about how long did the report run.

I am not sure about the report size, I'll have to check again.

Hope this helps.

Regards,

Rahul