Empty tables in Audit Universe unx File (No data)

[former_member203111]

Hi BOBJ Gurus,

I am trying to configure Audit database / universe in 4.1 SP2 and have imported .unx universe into IDT so I can run my audit activity report within organization.

I have configured Auditing in the CMC (Plz see Fig1 attached), I have almost selected everything as far as capturing the events are concerned.

I didn't change anything in the universe and published it in the repository and created a webi report accordingly. When I try to run the webi report based on objects like Application Type, Event Type, Event Status, user Name, Object Type etc, I dont see any deleted event type in the report. I am not sure whcih object do i need to capture the delete log.

I even created a sample report and deleted from the system but no log  in the report.

I checked the tables in the universe and there are some tables in unx file such as ADS_EVENT_DELETES, ADS_EVENT_DETAIL_DELETES and these tables are not joined with any other table and I also checked but there is no data in these tables. I even joined these tables and set an outer join so atleast i can see if there is any data but these tables are blank.

I am just wondering if these tables are supposed to be empty and Is there any other object that I need to use to capture and delete activity on the BOBJ side.

My question is that How do I go about and see Delete logs/activity in my webi report.

Will appreciate your help.

Regards

[former_member185603 · Accepted solution]

ADS_EVNET_DETELES-

Do not use or report off of this table. It is intended for internal system use, and may be removed in

future releases.

For Delete, use this table : ADS_EVENT_TYPE_STR.EVENT_TYPE_NAME , When you select the 'Event type' you shoud see the option for Delete.

Do you see the data for other events?

[former_member203111 · Accepted solution]

Hi Jawahar,

Thanks for your reply.

I checked in the IDT and used this table and the field Event_TYPE_Name and clicked on show column values but it doesnt have any data in it and no wonder there is nothing pulled in the report as well.

If I go to Event Type, I can see Refresh, Run and View option , thats it nothing else.

I am sharing the screenshot, probably you can let me know if there is an issue with join but in either case this field should show me data values.

What is the corresponding field of Event_Type_Name on the business layer/ or webi side, I mean the business name of this field. Thanks

[former_member185603 · Accepted solution]

Based on the screenshot, you are using the one it was published in SCN forum. Can you check if the auditing is processing or not?  Login to CMC- Select Auditing, See the valuee for 'ADS Last Updated on'. Does it show current date?

Check the connecition for the audit universe? is it pointing to right Audit DB configured in CMC?

And also you can run query on DB to see the when it was last updated?

Select max(start_TIME) from ADS_EVENT

[former_member203111 · Accepted solution]

Hi Jawahar

ADS Last updated on 9/16/14 at 5:01:06 PM which is current time

As far as connection for audit universe , its pointing to Microsoft SQL Server, What other option do i have , I mean is there any other server it needs to be pointed to.

My understanding is that any activity happening on BOBJ side should be captured based on SQL as BOBJ is installed on MS SQL

I will ask my DB admin to run the query on database side .

Thanks

[former_member185603 · Accepted solution]

Can you also check in CMC-Auditing. Is the Audit information in CMC and Audit universe connection matches or not?

[former_member203111 · Accepted solution]

Hi Jawahar,

Do you mean if the ADS database connection name i.e ODBC created to point to my SQL server is matching with Auditing.cns (secured connection) Can you please clarify.

Does these two names should be the same ?

[former_member185603 · Accepted solution]

ADS Connection name in CMS- Auditing should match with Data Source name in the Universe connection.

Login to IDT, open a connection (Audit connection) - You will see Data Source name.

And also make sure the auditing files in Auditing folder are processed. If they are processed, you should see the file size will be 0 KB.

[former_member203111 · Accepted solution]

Hi jawahar

My connection name and datasource name are the same, plz see the screenshot.

Also auditing files are processing in the auditing folder.

In the screenshot below, can you advise if the "Set Event Details" check boxes are suppose to checked , I just checked Query check box but it wont make any difference, What are these check boxes for ? Thanks

[former_member185603 · Accepted solution]

Now we know what are you missing.

You need to check the property value details if you want to see any modifications of the objects. After you make the change, restart SIA.

Check the admin Guide for more information. See the screenshot attached for information.

[former_member203111 · Accepted solution]

Hi Jawahar,

Thanks for your reply.

Does that mean I can select all check boxes and would be able to capture delete logs as well. I will go through the admin guide as well.

As far as I can see from the screenshot , the "Right Details" can capture Rights Removed but does that capture remove/delete log or else. can you please advise, In the meanwhile I will check the options and restart the SIA, thanks

[former_member185603 · Accepted solution]

Rights option will give only specific to rights., not deleting the objects. Property details will give you all the information you need.

[former_member203111 · Accepted solution]

Hi Jawahar,

I changed Set Event Details, (checked all) in the CMC but it wont make any difference.

One thing I also noticed that the ADS_EVENT_TYPE_STR.EVENT_TYPE_NAME  is still empty when I try show column values. Not sure why it is if this represent the Delete logs even after I delete the report.

I created an Input control in the webi report for Event Type not sure if this is the Event_TYPE_NAME or is there any other field I need to use but I dont see any delete option there, all I see is Refresh Run and View, please see the screenshot. Thanks

[former_member185603 · Accepted solution]

You need to run the query in DB and see all the event types are available.

This table will have all event types after you install the BO.

Can you ask your DBAs to run below query on the DB and check for the event types?

Select * from ADS_EVENT_TYPE_STR t where language ='EN'

[former_member203111 · Accepted solution]

I ran the Query and here is the result:

Event_Type_ID Language Event_Type_Name

1002 EN          View

1003 EN          Refresh

1004 EN          Prompt

1005 EN          Create

1006 EN          Delete

1007 EN          Modify

1008 EN          Save

1009 EN          Search

1010 EN          Edit

1011 EN          Run

1012 EN          Deliver

1013 EN          Retrieve

1014 EN          Logon

1015 EN          Logout

10003 EN          Rights Modification

10004 EN          Custom Access Level Modified

10006 EN          Auditing Modification

1016 EN          Trigger

[former_member185603 · Accepted solution]

You need to check the universe. Open universe in IDT and select show values on this table. It looks like something wrong with universe.

Refer this link for Audit universe download and how to configure..

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/40711187-04e8-2d10-d190-851e20c7665a

[former_member203111 · Accepted solution]

Hi Jawahar,

Actually i used the same link/blog and lcmBIAR file to configure my unx initially but it seemed like the file got corrupted the first time I imported and was not showing the complete data therefore I reconfigure everything again and ran a test report and it start working.

I am finally able to see complete logs.

I really appreciate your help and time , thanks

Regards

[former_member205064]

Do you see any file getting created in the Auditing folder and getting removed.?

or there are large number of files getting stack at the Auditing folder.

[former_member203111]

Hi Raunak,

I see lots of files generated in Auditing folder with 0kb , they are all blank and they are not removed , all sitting in the Auditing folder.

Note that I have set up auditing logs captured for last 90 days only in the CMC as compare to default 36500 days etc.

Thanks

[former_member205064]

Stop SIA.

remove the content from Auditing folder and put in some other location. (empty Auditing folder)

Start the SIA and check if the new files get created and getting removed as well.