Event Date Stamp in Audit database webi report

[former_member203111]

I have created some auditing reports in web intelligence using the audit universe provided by SAP

I am trying to capture a logon activity of the user and In this report I have used an object called Event Date Stamp which shows a data in such format for example Jan 5 2014 1:55PM, problem is that if a user logs on in the morning lets say around 9AM, the data stamp shows 2PM in the afternoon.

Initially I thought it could be my BOBJ server date (CMC date) but upon checking it is set up correctly.

I also checked my SQL server data and it is also set up correctly and showing current time.

Is it possible if this problem is coming from Universe side, or any other side other than BOBJ server and SQL server.

Can anyone advise please.

Thanks You.

[Former Member]

Its using the GMT for the time stamp. You can convert your time stamp back to local time at universe level as a possible solution for this.

Also check the kba below 1736216 - Audit Reports based off of Activity Universe show incorrect data due to audit database storing dates in GMT time zone Cause The audit database records all dates in the GMT time zone, regardless of settings elsewhere in the system.

Resolution For MySQL: Import the Activity Universe in Universe Designer: Import the current Universe from Enterprise -or- Using the Import Wizard, import the Activity Universe from the "auditing_db_mySql.biar" file located in \Samples, then import the fresh Activity Universe into the Universe Designer. Verify/Update the Universe connection by navigating to File>Parameters and selecting the correct connection to the auditing database. In the "Classes and Objects" pane, navigate to the Action class, and select the Action Time object. Open the object properties for the selected Action Time object Change the SELECT statement to the following (without the quotes): "date_sub(AUDIT_EVENT.Start_Timestamp, INTERVAL 4 HOUR)" NOTE: The above statement subtracts 4 hours from the GMT time zone, putting it in the EST time zone.  This value can be changed per each time zone. NOTE: The date_add function needs to be used in place of the date_sub function when the required time zone is ahead of GMT. EXAMPLE: For EET (GMT+1) the SELECT statement would be: "date_add(Audit_Event.Start.Timestamp, INTERVAL 1 HOUR)" Save the Universe and export it back to Enterprise. In InfoView or the Central Management Console - Navigate to Folders/Auditor/en/Least Accessed Documents/Least Accessed Documents - By Rimes Read Refresh the report with the same parameters listed above.  See correct results display in the report

[former_member203111]

Hi Durgamadhab Mishra,

Thanks for your reply.

Do I have to create a new object or can I use the existing one called "Event TimeStamp"

I am using 4.1 version of audit universe therefore there is no class called Actions there but I guess I can create the new dimension with "date" data type in any class and then in the select text box can write that new SQL function dbo.Get_UTC_Time(AUDIT_EVENT.Start_Timestamp)

can you please confirm, thanks

Regards

[Former Member]

Hi,

We also followed the same type of approach with our Auditing universe. The only way is to edit the universe and add the correct timestamp information. We used the same objects, so that the reports and everything that came with the universe just worked instantly.

I must say that I also converted the Crystal reports to Webi document rather as then I had the functionality to quickly change and fix anything.

Good Luck