cancel
Showing results for 
Search instead for 
Did you mean: 

Incorrect dates with activity universe?

Former Member
0 Kudos

Running a std audit report for "today" however, I'm getting counts for date/time 4 hrs in the future? I've read there is another "updated" activity universe we can get?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

There is a note with a workaround you can implement as by default the data is in GMT time:

1390845 - u201CAction Timeu201D object of Activity Universe is displayed in GMT how to convert to local GMT in Oracle database

Symptom

Regional Settings of Database, Connections, CMS are set to use a particular regional locale; however Action Time object is displayed in GMT (Greenwich Mean Time)

Reproducing the Issue

Set Regional Settings of Server OS, Central Management Server Database, Browser locale to a single locale

Create a Web Intelligence report using Activity Universe

Add object "Action Time" along with Action Event like "Action Name"

Execute this report

Action Time of an Event is displayed in GMT

Cause

Action Time object that is based on the database column AUDIT_EVENT.Start_Timestamp which is stored in GMT (Greenwich Mean Time).

Resolution

Open the Designer

Import the "Activity" universe

Open the class "Actions"

Right click and then select "Object" to add a new object

Set a name for this object, for example: "Action Time GMT-3"

Set the type of this object to: "Character"

In the "Select" text box type the following line: to_char(AUDIT_EVENT.Start_Timestamp -180/1440,'DD/MM/YY HH24:MI:SS')

Click on OK

Save and export the universe

The new object is ready to use in reports

Note: On the Step 7, you can convert the Action Time to any time zone you desire. The following formula is the general one:

to_char(AUDIT_EVENT.Start_Timestamp + (timezone*60)/1440,'DD/MM/YY HH24:MI:SS')

Where timezone must be replaced by the desired time zone. In the example above the GMT-3 was used, in this case:

to_char(AUDIT_EVENT.Start_Timestamp + (-3*60)/1440,'DD/MM/YY HH24:MI:SS')

Thus the following was used:

to_char(AUDIT_EVENT.Start_Timestamp + -180/1440,'DD/MM/YY HH24:MI:SS')

Former Member
0 Kudos

Here is one for if you are using MySQL for your CMS database:

1428277 - How to display the action time in EST time instead of the GMT time if the auditing database is on MYSQL

Symptom

The auditing database store the action time in GMT timezone. When view the auditing report, how to display the time in the EST timezone?

Reproducing the Issue

Create a Web Intelligence report using Activity Universe

Add object "Action Time" along with Action Event like "Action Name"

Execute this report

Action Time of an Event is displayed in GMT

Cause

Action Time object that is based on the database column AUDIT_EVENT.Start_Timestamp which is stored in GMT (Greenwich Mean Time).

Resolution

Open the Designer

Import the "Activity" universe

Open the class "Actions"

Right click and then select "Object" to add a new object

Set a name for this object, for example: "Action Time EST"

Set the type of this object to: "date"

In the "Select" text box type the following line: date_sub(AUDIT_EVENT.Start_Timestamp, interval 5 hour)

Click on OK

Save and export the universe

The new object is ready to use in reports

Note: On the Step 7, you can convert the Action Time to any time zone you desire, either use date_add or date_sub function in MYSQL:

For example, if you want to display the time in PST, which is GMT-8, you can use:

date_sub(AUDIT_EVENT.Start_Timestamp, interval 8 hour)

if you want to display the time in Beijing time, which is GMT+8, you can use:

date_add(AUDIT_EVENT.Start_Timestamp, interval 8 hour)

This is only valid if you use MYSQL as auditing database. If you are using oracle please refer note 1390845

Former Member
0 Kudos

John... Great, thanks so much!

Former Member
0 Kudos

The solution you provided assumes that my time zone is always the same number of hours different from GMT. However, in winter we're 5 hours behind and in summer we're 4 hours behind. In a user's preferences in InfoView, you can set the current time zone, and that time zone is used when displaying times, for example, the time when an instance ran. Is there some way to make the Activity universe use the setting in the user's preferences when displaying times? Or is there some other way to have the times displayed adjust for daylight time?

Thanks.

Laura Bollettino

Former Member
0 Kudos

Hello, John.

I have the same issue, but my Audit database is not Oracle neither MySQL. This is MS SQL Server. What can I do? reports are showing actions in a time that even hasn't happened yet and it really bugs me!

Regards

Erika

Former Member
0 Kudos

Hi.

I just have a request for clarity as to how to use it for indian standard time.

which is GMT + 5 Hrs and 30 minutes.

Can anyone explain in using this formula for indian timings. I just hope 330/1440 works. Since we are directly pushing this on production.

Also is this problem sorted out in service pack 4, or it would always be that, Audit universe timings would be showing only GMT.

Is it the same behaviour on recent 4.0 ?

Has anyone tested this.

thanks

indu

Edited by: Indumathy Narayanan on Jul 26, 2011 7:37 AM

Note For SQL Server 2008 Ent :

create a object ACTIONTIME IST as date :

In select : DateAdd(MINUTE,330,audit_event.Start_timestamp)

Click on tables and choose Audit_event table.

Click ok and then it will parse.

Else it would give error not knowing which table

Edited by: Indumathy Narayanan on Jul 26, 2011 10:50 AM

Answers (0)