Find the last login date for a user in Jira server
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Please be mindful that the information below is provided on a best effort as-is basis as it pertains to a service that is outside of Atlassian Support Scope. Any effort provided to support issues related to out of scope issues will be on a best-effort by the support engineer.
Purpose
Get a list of users showing their last login timestamp from the database in order to audit application usage.
This query is valid for either authentication through Jira GUI or REST API (Basic Authentication or PAT).
Please keep in mind that if the user has a "Remember Me" token set, then the Last Login date will not reflect the last time the user accessed JIRA, but will instead show the last time they had to go through the login process. See: JRA-60508 for details.
Process
For JIRA 7.0.x onwards
SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
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.lower_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;SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login"
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM cwd_user_attributes
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 attribute_value DESC;SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login"
FROM dbo.cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM dbo.cwd_membership m
JOIN dbo.licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM dbo.cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.ID
JOIN dbo.cwd_directory d ON u.directory_id = d.ID
ORDER BY "Last Login" DESC;
You might need to replace the schema name (.dbo) with the schema used by your MS-SQL database (i.e jiraschema).
SELECT d.directory_name,
u.user_name,
TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID
) m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc;
For JIRA 6.2 to 6.4
Run the query specific to your database of choice as shown below:
SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
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 globalpermissionentry gp ON m.lower_parent_name = gp.group_id
WHERE gp.permission IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
) AS m ON m.child_name = u.user_name
LEFT 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;SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login"
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN globalpermissionentry gp ON m.lower_parent_name = gp.group_id
WHERE gp.permission IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
) AS m ON m.child_name = u.user_name
LEFT JOIN (
SELECT *
FROM cwd_user_attributes
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;SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login"
FROM dbo.cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM dbo.cwd_membership m
JOIN dbo.globalpermissionentry gp ON m.lower_parent_name = gp.GROUP_ID
WHERE gp.PERMISSION IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
) AS m ON m.child_name = u.user_name
LEFT JOIN (
SELECT *
FROM dbo.cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.ID
JOIN dbo.cwd_directory d ON u.directory_id = d.ID
ORDER BY "Last Login" DESC;SELECT d.directory_name,
u.user_name,
TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN globalpermissionentry gp ON m.lower_parent_name = gp.GROUP_ID
WHERE gp.PERMISSION IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
) m ON m.child_name = u.user_name
LEFT JOIN (
SELECT *
FROM cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc
For JIRA 4.3 to 6.1.9
Relevant tables are cwd_user and cwd_user_attributes for obtaining information on this. Run the query specific to your database as shown below.
The queries in this section will not function as expected when using an external User Management system like Atlassian Crowd or any other LDAP service.
SELECT cwd_user.user_name, from_unixtime(round(cwd_user_attributes.attribute_value/1000))
FROM cwd_user, cwd_user_attributes
WHERE cwd_user_attributes.user_id = cwd_user.id
AND cwd_user_attributes.attribute_name = 'lastAuthenticated';SELECT cwd_user.user_name, to_timestamp(CAST(cwd_user_attributes.attribute_value AS bigint)/1000)
FROM cwd_user, cwd_user_attributes
WHERE cwd_user_attributes.user_id = cwd_user.id
AND cwd_user_attributes.attribute_name = 'lastAuthenticated';SELECT cwd_user.user_name, dateadd(second,cast(cast(cwd_user_attributes.attribute_value AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000')
FROM cwd_user, cwd_user_attributes
WHERE cwd_user.id = cwd_user_attributes.user_id AND cwd_user_attributes.attribute_name = 'login.lastLoginMillis'SELECT cwd_user.*,
to_date('01.01.1970','dd.mm.yyyy') + to_number(cwd_user_attributes.attribute_value)/1000/60/60/24 AS last_login
FROM cwd_user, cwd_user_attributes
WHERE cwd_user.id = cwd_user_attributes.user_id
AND cwd_user_attributes.attribute_name ='login.lastLoginMillis' AND cwd_user.ACTIVE ='1'Add schema name to the query when necessary.
Option 2 - For JIRA 7.0.x onwards
If you want to add the "active" and "never logged in" users to this list, consider the following query:
SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
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.lower_parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
LEFT 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;Counting Unique Users Active in the Last 6 Month
SELECT COUNT(DISTINCT cu.id) AS "Unique User Count"
FROM cwd_user_attributes ca
JOIN cwd_user cu ON cu.id = ca.user_id
WHERE ca.attribute_name = 'login.lastLoginMillis' AND
CAST(TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL(ca.attribute_value/1000, 'SECOND') AS DATE) >= TRUNC(SYSDATE) - INTERVAL '6' MONTH;