How to check for users who never logged into Jira Data Center

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.

Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. 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

Purpose

There are times when the Jira administration would like to see the users who never logged into the application for administrative purposes.

Environment

Jira Data Center on any version from 8.0.0.

Solution

In order to see the users who never logged into Jira, run the following query:

This was tested with PostgreSQL and Jira 8.20.2. Other databases may slightly differ the syntax.

SELECT LOWER_USER_NAME
FROM CWD_USER
WHERE LOWER_USER_NAME not in
		(SELECT CWD_USER.LOWER_USER_NAME
			FROM CWD_USER,
				CWD_USER_ATTRIBUTES
			WHERE CWD_USER_ATTRIBUTES.USER_ID = CWD_USER.ID
				AND CWD_USER_ATTRIBUTES.ATTRIBUTE_NAME = 'login.count');

In order to see the users who are members of the jira-software-users group but never logged into Jira run the following query:


SELECT DISTINCT u.lower_user_name
FROM   cwd_user u
       JOIN cwd_membership m
         ON u.id = m.child_id
            AND u.directory_id = m.directory_id
       JOIN licenserolesgroup lrg
         ON Lower(m.parent_name) = Lower(lrg.group_id)
       JOIN cwd_directory d
         ON m.directory_id = d.id
WHERE  d.active = '1'
       AND u.active = '1'
       AND license_role_name = 'jira-software'
       AND u.lower_user_name IN (
           SELECT CWD_USER.LOWER_USER_NAME
           FROM CWD_USER
           WHERE LOWER_USER_NAME NOT IN (
               SELECT CWD_USER.LOWER_USER_NAME
               FROM CWD_USER
                    JOIN CWD_USER_ATTRIBUTES
                    ON CWD_USER_ATTRIBUTES.USER_ID = CWD_USER.ID
               WHERE CWD_USER_ATTRIBUTES.ATTRIBUTE_NAME = 'login.count'
           )
       );

Last modified on Jan 21, 2025

Was this helpful?

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