Project access history

[Tuomas P]

Hi,
Is there any other way, than going trough access_log in Jira, if I want to see if user has accessed certain project?

I know that they have had the project access, but I need to know if they really have accessed (i.e. opened) the project.

My only idea was to go trough access_log to see if they have accessed the project URL...

[Chander Inguva]

@Tuomas 

 Not sure if this helps you

select entitytype,entityid,USERNAME,lastviewed from userhistoryitem WHERE entitytype='Project'

For getting Date Time, use

-- Query 1:
-- Select Project ID for a Project Key "JRA"
select ID from project WHERE pkey='JRA'

-- Query 2
select entityid,entitytype,USERNAME, DATEADD(S, CONVERT(int,LEFT(lastviewed, 10)), '1970-01-01') AS Last_Viewed from userhistoryitem WHERE entitytype='Project' AND entityid =<Project ID from QUERY 1> ORDER BY Last_Viewed DESC

Note : Time returned is in UTC

Thanks

Chander

[Mehboob Salim Shaikh]

Please run the below for getting the desired output.

Oracle SQL

 select entitytype,entityid,USERNAME,pname,pkey, TO_CHAR( FROM_TZ( CAST(DATE '1970-01-01' + (1/24/60/60/1000) * lastviewed AS TIMESTAMP), 'America/New_York'), 'MM/DD/YYYY HH24:MI:SS')
 from userhistoryitem t1,project t2 where entitytype='Project'
and t1.entityid = t2.id;

PostgreSQL

 select t1.entitytype,t1.entityid,t1.USERNAME,t2.pname,t2.pkey,to_timestamp(cast(cast(lastviewed as varchar(10)) as bigint)) as date_time 
 from userhistoryitem t1,project t2 
where entitytype ='Project'
and (t1.entityid)::int = t2.id
order by date_time desc;