How to find when the report was last accesed

system · May 15, 2009, 9:48am

Hi Im using BO 6.5
I would like to find the when the report was last accessed. we have a universe called “Repository Analysis” in that universe i could see the object “Doc Last Accessed Date” and the correspondin field in the database is “Document.M_DOC_N_LAD” and datatype is Number

But when i include this field in the report and runs the report under the “Doc Last Accessed Date” column im getting values like this “1209636949”

Kindly let me know how we can find out the corresponding Last accessed date from this number value.

Ajithsankar (BOB member since 2008-10-19)

system · May 15, 2009, 12:42pm

Hi,

The date in that column is stored in a unix-like format. It’s a number of seconds from the midnight of 15-DEC-1970 (it means 1 second after 16-DEC-1970 23:59:59). Use your database date function(s) to do the math and convert it to a real date.

Marek Chladny (BOB member since 2003-11-27)

system · May 15, 2009, 4:04pm

Hi

Thanks for your reply

i am using Oracle 9i, can u please help me in getting the syntax to convert the these seconds to Date.

Ajithsankar (BOB member since 2008-10-19)

system · May 15, 2009, 4:20pm

Try the following:

TO_DATE('19701215','yyyymmdd') + M_DOC_N_LAD/86400

Marek Chladny (BOB member since 2003-11-27)

system · May 18, 2009, 7:37am

Hi Marek

Thanks for ur support.

I did create a object in Repository Analysis universe with the followin g syntax.
(TO_DATE(‘19701215’,‘yyyymmdd’) +Document.M_DOC_N_LAD/86400)

and when i run the report including this object it is giving me the desired result.

Can u please explain me the logic behind the above formula.

Ajithsankar (BOB member since 2008-10-19)

system · May 19, 2009, 2:39am

But I already did that

and as a hint, 86400 = 24 * 60 * 60 is the number of seconds in 1 day.

Marek Chladny (BOB member since 2003-11-27)

system · May 21, 2009, 12:06pm

Hi Marek

One more thing

i recently created a user and when i use the last accessed Date,document name and user name in the report.

and the result shows this user has accessed this report on some month in 2007, that user was not even created in 2007.

Now let me know, whether the formula has to be changed or the data in the repository is wrong.

waiting for your reply.

Ajithsankar (BOB member since 2008-10-19)

system · May 21, 2009, 2:26pm

Hi

How do you link users with reports that they accessed? I think this is not possible in the BO repository data. Maybe in Audit data but not in the repository.

Marek Chladny (BOB member since 2003-11-27)

system · May 26, 2009, 2:13pm

Hi Marek

I am using a universe (Repository Analysis) we got it from BO. in that universe i found username, document name and various fields related to repository. and in my report i used userid from the universe itself. and its giving me the wrong reslts.

Can you let me know whether im using the correct universe to get the required data (When the report was last accessed)

or is there any other way to find the when the report was last accessed

Thanks

Siva

Ajithsankar (BOB member since 2008-10-19)

system · May 27, 2009, 12:07am

Hi,

I am sorry but I have no idea what the universe you have is about.

Is it using Audit tables? Or only BO repository tables?

Marek Chladny (BOB member since 2003-11-27)

system · May 27, 2009, 11:29am

Hi Marek

Thanks for ur support.

Im attaching the screen shot of the Repository univ which im using to take the report.

kindly let me know is that the univ i need to use to create the report.

pls let me know if u ned more details

Thanks for ur time and consideration

Siva.G
Repository univ screenshot.JPG

Ajithsankar (BOB member since 2008-10-19)

system · May 27, 2009, 12:52pm

Hi,

It looks that the universe is based on the BO repository tables and not based on the Audit tables.

From the BO repository tables you can get “doc last accessed date” for a BO document. In fact, I can see such object in the universe on the picture. However, I presume that the object is based on M_DOC_N_LAD column of the OBJ_M_DOCUMENTS - this column gives a date when the document was published or modified the last time. Not the time when a user used the report last time.

You need to use Audit tables (all tables that start with OBJ_A_…). And provided that you have turned on auditing in past so the tables are not empty but contain data of users actions in the BO system.

Marek Chladny (BOB member since 2003-11-27)

system · June 1, 2009, 11:50am

Hi Marek

Thanks for your reply
I have some tables in the universe which Starts OBJ_A.(Please find the attachment)
And i could not find any objects which will give me Last Accessed Date.
and can you please let me know how to find out whether Auditor was turned on in the past?

And kindly let me know how to find out when the report was last accessed

Ajithsankar (BOB member since 2008-10-19)

system · June 1, 2009, 12:03pm

Hi,

Check OBJ_A… tables - is there any data? Review mainly OBJ_A_EVENT_LOG and OBJ_A_EVENT_DETL.

Marek Chladny (BOB member since 2003-11-27)

system · June 2, 2009, 10:03am

Hi Marek

When i check the columns in the Event Log and Event Details, i found following columns

OBJ_A_EVENT_DETL

A_EVENT_N_ID
A_EVENT_N_OBJECTID
A_EVENT_C_OBJNAME
A_EVENT_N_OBJTYPID

OBJ_A_EVENT_LOG

A_EVLOG_N_ID
A_EVLOG_N_TYPEID
A_EVLOG_D_STARTTIME
A_EVLOG_N_DURATION
A_EVLOG_C_USER
A_EVLOG_C_SESSION
A_EVLOG_N_APPID
A_EVLOG_N_ERRORID
A_EVLOG_C_HOST
A_EVLOG_N_CLSTRID

Can you please let me know which fields will be used to find when the report was last accessed?

Thanks

Siva

Ajithsankar (BOB member since 2008-10-19)

system · June 3, 2009, 2:13am

Hi Siva,

Review this audit universe. You may find it useful:

Marek Chladny (BOB member since 2003-11-27)