Find the last login date for a user in Jira server

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

This article requires fixes

This article has been Flagged for fixing. Use caution when using it and fix it if you have Publisher rights.

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.

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

(info) You might need to replace the schema name (.dbo) with the schema used by your MS-SQL database (i.e jiraschema).

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.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:

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

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.


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:

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





DescriptionGet a list of users showing their last login timestamp from the database in order to audit application usage.
ProductJira
PlatformServer
Last modified on Mar 1, 2024

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.