nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

List users by previous login date -jira

unknown

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

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

Please assist,

Thanks

Shelly

Accepted answers

Sreenivasaraju P

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

All comments

Sreenivasaraju P

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

unknown

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server