List users by previous login date -jira

[Deleted user]

Hello,

I want to create a report of List of users by previous login date with active/inactive userws and their full name. from jira DB with postgres.

I found this article for confluence that provide the list of users with previous login date. didnt find for jira.

https://confluence.atlassian.com/confkb/how-to-identify-inactive-users-in-confluence-214335880.html

SELECT cu.user_name,
       li.PREVsuccessdate
FROM logininfo li
JOIN user_mapping um ON um.user_key = li.username
JOIN cwd_user cu ON um.username = cu.user_name
ORDER BY PREVsuccessdate;

Please assist,

Thanks

Shelly

[Sreenivasaraju P · Accepted solution]

Hi @[deleted] ,

You can refer below link, it can give the which table to be referred.

https://confluence.atlassian.com/jirakb/identify-users-in-jira-who-haven-t-logged-in-for-the-past-90-days-695241569.html

[Deleted user]

Thanks @Sreenivasaraju P - I saw that but it gives me only 90 days.
I have this for the last login, and I am searching for the previous login date.

Should be a small change which I am missing.

SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
u.display_name AS "Display Name",
concat(u.first_name,' ',u.last_name) AS "Full Name",
u.active AS "active",
to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login"
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;

Thanks.

[Sreenivasaraju P]

SELECT d.directory_name AS "Directory",
u.user_name AS "Username",u.active,
FROM_UNIXTIME((CAST(attribute_value AS UNSIGNED)/1000)) AS "Last Login"
FROM cwd_user u
JOIN cwd_directory d ON u.directory_id = d.id
LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis' order by FROM_UNIXTIME((CAST(attribute_value AS UNSIGNED)/1000)) DESC;

try the above query. This is for mysql