Retrieve last login dates for users from the database

Still need help?

The Atlassian Community is here for you.

Ask the community

This Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Functional differences in Atlassian Cloud, the contents of this article cannot be applied to Atlassian Cloud applications.

Purpose

Get a list of users showing their last login timestamp from the database in order to audit application usage.

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

Postgresql
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.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;
Mysql
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.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 "Last Login" DESC;
MSSQL
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.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;
Oracle
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.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+

Run the query specific to your database of choice as shown below:

PostgreSQL
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.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;
MySQL
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.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;
MSSQL
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.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;
Oracle
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.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.

MySQL
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';
PostgreSQL
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';
Microsoft SQL Server
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'
Oracle SQL
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.





Description Get a list of users showing their last login timestamp from the database in order to audit application usage.
Product Jira
Platform Server
Last modified on Dec 4, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.