How to get a list of valid, licensed users and information about their last login date and/or if they never logged in via SQL.

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

Summary

This article will help you to get a variety of useful information about your users from the database:

  1. A list of users that are counted against your Confluence license, excluding duplicates.
  2. Users that count towards the license who never logged in.
  3. Users that count towards the license who last logged in x months ago.


This information can be helpful for administrators looking to reduce the amount of users who count toward their license based on usage activity and last login information.

Environment


Tested using Confluence 7.13.x

Solution

  1. First run the following SQL Query to create a temp table with the name "temp_lic_user" which will include all valid (a.k.a have their permission set:USECONFLUENCE) licensed users. 
CREATE table temp_lic_user as (SELECT distinct(u.lower_user_name)
FROM cwd_user u
JOIN cwd_membership m ON u.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
JOIN cwd_directory d on u.directory_id = d.id
WHERE sp.PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T')

2. Next, run this SQL Query to get a list of licensed users who have never logged in (Note that we will be using the above SQL-generated temp_lic_user table) to Confluence.

SELECT * FROM user_mapping WHERE user_key NOT IN(SELECT username FROM logininfo where username in 
(SELECT um.user_key FROM user_mapping um, temp_lic_user cu WHERE cu.lower_user_name=um.lower_username))

3. Finally, run the SQL Query to get the licensed users who have a last login date of anything before '2024-03-04'. (Note: You will need to modify the date to meet your requirements.)  (Note that again, we will be using the above SQL query generated temp_lic_user table).

SELECT * FROM logininfo WHERE username in (SELECT um.user_key from user_mapping um, temp_lic_user cu where cu.lower_user_name=um.lower_username)
AND successdate > '2024-03-04'






Last modified on Jun 1, 2024

Was this helpful?

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