We have upgraded to XI3.1. I configured to use the Auditing DB. When running some of the Auditing reports I have notice that the Action Time for example, is the GMT time. We are on US EDT and our Webi reports reflect the correct time. Is there a way to change Auditing to use EDT instead of GMT?
Any response would be appreciated.
Thanks,
Mike
usmwi 
(BOB member since 2007-02-28)
Mike,
I just tried a work around for this by tweaking up the Universe Objects to adjust for the timezone
If your Auditing DB is MS SQL
try this
dateadd(hour,-datediff(hour,getdate(),getutcdate()),AUDIT_EVENT.Start_Timestamp)
Also there is an issue I realized with Action time and Session Time, where if you edit the object in its query panel…you’ll see that there is a condition set on Action time with Date as 1/1/2008 12:00:00 PM incase you see an empty LOV while running reports, taking the above condition off would help you.
Cheers,
Vinod
chhaps (BOB member since 2006-11-03)
Vinod,
Thanks for the response. I will try what you suggested. We are using SQL server 2005.
Thanks,
Mike
usmwi (BOB member since 2007-02-28)
Vinod,
Your work around worked great!
Thanks again,
Mike
usmwi (BOB member since 2007-02-28)
Sure Mike
chhaps (BOB member since 2006-11-03)
Hi Vinod,
Would you please be a little bit more specific about where to put your code (we are using same db):
“dateadd(hour,-datediff(hour,getdate(),getutcdate()),AUDIT_EVENT.Start_Timestamp)”
Please explain what do you mean by “query panel”
“Also there is an issue I realized with Action time and Session Time, where if you edit the object in its query panel…you’ll see that there is a condition set on Action time with Date as 1/1/2008 12:00:00 PM incase you see an empty LOV while running reports, taking the above condition off would help you.”
Thank you!
nlajka (BOB member since 2008-07-31)
Hi,
Specify the date SQL in the Activity Universe. The example below in the above post, is for the ‘Action Time’ dimension.
About the 2nd issue - When you are on the Action Time Field, navigate to the properties tab and click edit, you’ll see that 2nd issue and the work around which I had implemented.
Thanks,
Vinod
chhaps (BOB member since 2006-11-03)
What would be the way to make an object for DB2 UDB database? I’ve tried a variety of coding and functions, just can’t get BOBJ to like it.
My specific desire- in the Auditor report that indicates last logon for a user-the lastlogon is an object Max([Action Time]). I need to make a new object for CDT, or CST by subtract 5 or 6 hours from that.
I’m wondering - is it as difficult as trying to do something like the noted forum entry of Mareks that converts a time to seconds after a designated zerodatetime then subtracts a known time of 18,000 seconds and then reformating into timestamp?
https://bobj-board.org/t/117814
Any other suggestions?
benslow (BOB member since 2005-11-04)
Hi,
Regarding the same problem (auditing timezone in BOEXI 3.1), is there any way to make CMS to track the auditing using the server timezone (CST or EST)??
I hope BO should take care of this with a configuration switch.
In BOEXI R2, when we ran Auditing reports, the action time was coming in the server time zone!!! dont know how??? BO tech support shows us the admin guide & say even in R2 it was using UTC time zone!!
Not sure how our reports were working even without any change on the Activity universe!!
Thanks in advance for any pointers on this.
-CK
Chandru 901 
(BOB member since 2007-07-20)
Did anybody figure out how to set the time zone for auditing at a server level? Is there a change we can make somewhere, like a switch or web.xml file? Or are we stuck doing it within the Activity universe?
ccermak (BOB member since 2007-11-13)
Hello Chhaps
I know this is bit old post, but i am trying to see if anyone can help me on this time zone issue. We have oracle database for Activity universe(BOXI 3.1).
I would want to create a column at the database level to convert the values from the start.timestamp from GMT to PST. I just found this thread where you had found a solution to SQL Server DB but just wondering do you have any solution for Oracle DB.
And in your reply you mentioned that you tweeked the objects at the universe and can you be little more specific on how to do this for example we have objects like Action time and the syntax that it has is
LPAD(RTRIM(TO_CHAR(AUDIT_EVENT.Start_Timestamp,‘WW’), CHR(0)),2,‘0’)
So should i change it to
LPAD(RTRIM(TO_CHAR(dateadd(hour,-datediff(hour,getdate(),getutcdate()),AUDIT_EVENT.Start_Timestamp,‘WW’), CHR(0)),2,‘0’)
Is that correct?
sanappi (BOB member since 2010-05-03)
Hello all, i try to replace all the places where i found
AUDIT_EVENT.Start_Timestamp
with
AUDIT_EVENT.Start_Timestamp - (((sys_extract_utc( to_timestamp_tz( sysdate, ‘DD-MM-YYYY HH24:MI:SS TZR’ )) - sysdate)))
it has started to work fine execpt that i am try to fix the objects in session time classes i am stuck.
I am trying to edit the derived table syntax by trying to replace just what i did above but now i am getting an error that i have attached here. So can anyone suggest me how to fix the time objects in the session classes.
sanappi (BOB member since 2010-05-03)
Hi,
I solved this in a view, and use the view in the universe (just do rename table and give the name of the view).
oracle:
create view V_audit_event as
select
EVENT_ID ,
USER_NAME ,
start_timestamp +(substr(DBTIMEZONE,2,2))/24 START_TIMESTAMP,
DURATION,
EVENT_TYPE_ID,
SERVER_CUID,
OBJECT_CUID,
OBJECT_TYPE,
ERROR_CODE,
OBJECT_ID from audit_event;
meutte 
(BOB member since 2007-12-06)
Its been long time, I am coming back to this issue… If we edit the Action Time object in the Activity universe to have the same calculation used in the above view, it works the same way. Do you foresee any issues or disadvantages of doing so? Please let me know
Thanks,
CK
Chandru 901 (BOB member since 2007-07-20)