SQL query to find all users who never logged in

Question

Hi, Do you know how can I get the list of users who never logged in into Confluence, while Confluence is connected to JIRA directory. This query does not seem to work as it brings me users who actually logged in What is the right way to find these users? Many thanks! select user_name from cwd_user where user_name not in (SELECT cwd_user.user_name FROM cwd_user, cwd_user_attribute WHERE cwd_user_attribute.user_id = cwd_user.id AND cwd_user_attribute.attribute_name = 'lastAuthenticated');

Om Joshi_8c083n · Answer

hi is there anything for bitbucket?

Rodolfo Romero - Adaptavist · Answer

The correct query should use the 'login.count' attribute instead. Like this.

SELECT LOWER_USER_NAME as username, lower_email_address as email, lower_display_name as displayname
FROM CWD_USER 3WHERE 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');

Manuel_9gzgju · Answer

I have same issue like Simha. The provided sql shows user that do have a last login date.