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; } } }

I want to identify which users have not logged in past 12 months (JIRA)

Rahul Aich [Nagra]

I want to identify which users have not logged in past 12 months .

Which database table stores the last logged in date/time information?

I am on jira 6.1

Rahul

Accepted answers

Vijay Khacharia

Hi,

Here is the JIRA KB that you help you to do it.

https://confluence.atlassian.com/display/JIRAKB/Retrieve+last+login+dates+for+users+from+the+database

Vijay

All comments

Vijay Khacharia

Hi,

Here is the JIRA KB that you help you to do it.

https://confluence.atlassian.com/display/JIRAKB/Retrieve+last+login+dates+for+users+from+the+database

Vijay

Matheus Fernandes

Hi Rahul

I've been trying this locally, and the following query should return you a list of users who have either not logged into JIRA at all, or have not logged in after Jan 1st:

SELECT  u.user_name as "Username", 
	u.display_name as "Full Name", 
	to_timestamp(CAST(a.attribute_value as bigint)/1000) as "Last Login" 
FROM cwd_user u
	LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis' 
WHERE a.attribute_value >= '1388541600000' OR a.attribute_value IS NULL
ORDER BY a.attribute_value;

<sup>I've created this using PostgreSQL, so it may need changes if using different DBMS.</sup>

If you want to change the date, simply change the value in the WHERE clause to a different date (in milliseconds). The following website come in handy to convert the date to milliseconds: http://www.ruddwire.com/handy-code/date-to-millisecond-calculators/.

I hope this helps!

Rahul Aich [Nagra]

thanks Vijay that helps

Rudy Holtkamp

For reference, the MYSQL query looks like this:

SELECT  u.user_name as 'Username', 
        u.display_name as 'Full Name', 
        from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) as 'Last Login' 
FROM cwd_user u 
        LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis' 
WHERE a.attribute_value >= '1388541600000' OR a.attribute_value IS NULL 
ORDER BY a.attribute_value;

Welcome!

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

Quick Links

Find more posts tagged with

jira

jira-administration