How do I list all user in a project as well as their last login date and time ?

[Ronny]

Hi there,

I try to pull up some data for our users in a specific project in terms of their last login date.

Our database is in postgresql, Im just trying to find a query to do so.

I can pull the list of user for our specfic project but Im having dificulty in finding the right query to add "last login date" on that query.

[Alexey Matveev · Accepted solution]

Hello,

You can find an answer in this KB:

https://confluence.atlassian.com/jirakb/retrieve-last-login-dates-for-users-from-the-database-363364638.html

[Ronny]

Hi there,

I have actually checked this solution, however this query will show for all Jira users.

I just need the specific users from a project.

[Ronny]

I tried this query but it does not work, :

SELECT A.user_name, to_timestamp(CAST(B.attribute_value AS bigint)/1000)
FROM cwd_user A , cwd_user_attributes B
WHERE B.user_id = A.id
AND B.attribute_name = 'lastAuthenticated'
INNER JOIN
projectroleactor P
ON A.USER_NAME = P.ROLETYPEPARAMETER
where pid = '16026'

[Alexey Matveev]

I would write this query like this:

SELECT A.user_name, to_timestamp(CAST(B.attribute_value AS bigint)/1000)
FROM cwd_user A , cwd_user_attributes B, projectroleactor P
WHERE B.user_id = A.id
AND B.attribute_name = 'lastAuthenticated'
AND  A.USER_NAME = P.ROLETYPEPARAMETER
AND pid = '16026'

[Ronny]

Thank you @Alexey Matveev

It Works :)

[Alexey Matveev]

You are welcome!