Audit Timestamp

system · January 17, 2008, 4:17pm

Environment:
Business Objects Enterprise XI R2 SP2 Premium
Windows 2003
Tomcat
Audit DB set up on SQL Server 2005

Ok, I got all the initial issues straightened out with the Activity universe and connection and I can run the sample CR and WebI reports successfully. The interesting thing I have noticed in the Audit report data is that the timestamp is off by 5 hours. It looks like it is stamping GMT, when I think it should be US EST (-5 hours), which is what is set for Eastern Time (-5). Is there anything I am missing to get Auditor to stamp the correct time? Is it a setting in Tomcat maybe?

breed (BOB member since 2007-11-14)

system · January 24, 2008, 3:06pm

After several days of waiting for Business Objects Support to respond, I finally got an answer. Bottom line is that the Audit timestamp is in UTC and there isn’t anything you can do to change that. The solution is to add or subtract from that the number of hours difference your web server is from GMT, and you have to do this in the Universe or Crystal Report formula, or SQL.

breed (BOB member since 2007-11-14)

system · December 12, 2008, 10:24pm

Yes, I discovered this myself about the Audit timestamps just recently. The audit database is on SQL Server, so here is what I did wherever AUDIT_EVENT.Start_Timestamp was found:

dateadd(hour,-datediff(hour,getdate(),getutcdate()),AUDIT_EVENT.Start_Timestamp)

lgonzalez (BOB member since 2002-07-17)

JohnBClark · January 7, 2009, 10:32pm

If you use Crystal Reports there is a really cool new function with Crystal Reports XI R2. It’s called ShiftDateTime. It allows you to adjust your times for time zone and for daylight savings time. This is what I have used for some of my reports. I don’t know if it is available for Web Intelligence or Desktop Intelligence though.

system · September 23, 2009, 12:31am

Hi Everyone,
Can anyone please tell me how to write an eqivalent code for adjusting Audit timestamps in ORACLE.

lgonzalez:

Yes, I discovered this myself about the Audit timestamps just recently. The audit database is on SQL Server, so here is what I did wherever AUDIT_EVENT.Start_Timestamp was found:
dateadd(hour,-datediff(hour,getdate(),getutcdate()),AUDIT_EVENT.Start_Timestamp)

Thanks

forgotUN (BOB member since 2006-12-13)

system · September 23, 2009, 3:40am

Hi.

I have not tested, but I think this might work:

CAST(AUDIT_EVENT.Start_Timestamp AS TIMESTAMP WITH LOCAL TIME ZONE)

lgonzalez (BOB member since 2002-07-17)

system · September 23, 2009, 5:08am

Hi lgonzalez,
Thanks for you response.
Its not working

Thanks

forgotUN (BOB member since 2006-12-13)

system · September 23, 2009, 3:04pm

You’ll have to give me a little more than that. Any error messages? If not, what were the results?

lgonzalez (BOB member since 2002-07-17)

system · September 24, 2009, 2:40am

Hi lgonzalez,
Appyling CAST is not changing the timestamp.
For example,
when i excute the below sql without CAST:
SELECT DISTINCT AUDIT_EVENT.Start_Timestamp FROM AUDIT_EVENT
Result is like 02-07-2009 03:21:53
Adding CAST
SELECT DISTINCT CAST(AUDIT_EVENT.Start_Timestamp AS TIMESTAMP WITH LOCAL TIME ZONE) FROM AUDIT_EVENT
Results is like 02/JUL/09 03:21:53.000000000 AM

Its just adding fractional part to the timestamp

Thanks

forgotUN (BOB member since 2006-12-13)

system · September 24, 2009, 5:08am

Hi.

Sorry, I don’t have an Oracle system to test.

I found an Oracle function that may help. This example converts the audit timestamp to PST:

TO_CHAR( NEW_TIME( AUDIT_EVENT.Start_Timestamp ,'GMT','PST'), 'MM-DD-YY HH24:MI:SS')

Problem is you’ll have to change it with every time change to/from Daylight Savings Time. Here are the time zone codes:

ADT 	Atlantic Daylight Time
BST 	Bering Standard Time
BDT 	Bering Daylight Time
CST 	Central Standard Time
CDT 	Central Daylight Time
EST 	Eastern Standard Time
EDT 	Eastern Daylight Time
GMT 	Greenwich Mean Time
HST 	Alaska-Hawaii Standard Time
HDT 	Alaska-Hawaii Daylight Time
MST 	Mountain Standard Time
MDT 	Mountain Daylight Time
NST 	Newfoundland Standard Time
PST 	Pacific Standard Time
PDT 	Pacific Daylight Time
YST 	Yukon Standard Time
YDT 	Yukon Daylight Time

You may want to test if it works with region names like ‘US/Pacific’ which won’t force you to change with Daylight Savings.

Else the following I think is the equivalent of what I did for SQL Server:

AUDIT_EVENT.Start_Timestamp - ((sys_extract_utc( to_timestamp_tz( sysdate ) ) - sysdate)/24)

lgonzalez (BOB member since 2002-07-17)

system · September 25, 2009, 3:50am

Hi lgonzalez,
I appreciate for your time and effort. Thanks a lot, it worked with Option 2:
I have to change the code bit to get it worked.
Adjusting Audit timestamps in ORACLE

AUDIT_EVENT.Start_Timestamp-(((sys_extract_utc( to_timestamp_tz( sysdate,'DD-MM-YYYY HH24:MI:SS TZR' ))- sysdate)))

Modified all Time related Objects in Activity Universe with above code and everything went well…

Thanks
-Satish

forgotUN (BOB member since 2006-12-13)

system · October 14, 2009, 7:04am

Hi All,

Same problem is coming for me also, but here i am using Audit database in MYSQL, so some syntax error(Parsing Error) is coming.

please anyone help for me.

masilabalu (BOB member since 2006-10-02)

system · October 15, 2009, 1:26am

Hi,

Same Problem ? Need some details
What you are trying to achieve ? and what Sql did you use ?

Thanks

forgotUN (BOB member since 2006-12-13)

system · October 21, 2009, 12:19am

Im not sure if this will work because it doesnt take care of the GMT-PST/PDT (Im taking Pacific as an example) cutoff time, ie -7 hrs before Nov 1st sunday and -8 hrs after Nov 1st Sunday.

This will simply convert everything based on the date when the report was run and apply that hour difference (-7hrs or -8hrs) even for historical audit logs irrespective of PDT or PST

dateadd(hour,-datediff(hour,getdate(),getutcdate()),AUDIT_EVENT.Start_Timestamp)

dudeus (BOB member since 2006-05-24)