Active users in Atlassian apps

13 Oct, 2022

Sometimes there is a need to analyse active users of an application. Atlassian applications do not provide such information from the application administrator interface. However, this data can be extracted from the database.

Unfortunately, due to slightly different table structures, there is no single universal query for all applications. Differences exist between the implementation of the active column or the name of the attribute that stores the epoch time of the user's last login.

Below are the queries for each application.

Confluence

with lastlog as (
select u.user_name username, u.lower_email_address email, 
to_date('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(a.attribute_value/1000, 'SECOND') as lastlog
from cwd_user u, cwd_user_attribute a, cwd_directory d
where u.directory_id = d.id
  and d.active='T'
  and a.attribute_name = 'lastAuthenticated'
  and u.id = a.user_id
)
select * from lastlog;

Jira

with lastlog as (
select u.user_name username, u.lower_email_address email, 
to_date('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(a.attribute_value/1000, 'SECOND') as lastlog
from cwd_user u, cwd_user_attributes a, cwd_directory d
where u.directory_id = d.id
  and d.active=1
  and u.active=1
  and a.attribute_name = 'lastAuthenticated'
  and u.id = a.user_id
)
select * from lastlog;

Bamboo

with lastlog as (
select u.user_name username, u.lower_email_address email, 
to_date('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(a.attribute_value/1000, 'SECOND') as lastlog
from cwd_user u, cwd_user_attribute a, cwd_directory d
where u.directory_id = d.id
  and d.active='T'
  and u.active='T'
  and a.attribute_name = 'lastAuthenticated'
  and u.id = a.user_id
)
select * from lastlog;

Bitbucket

with lastlog as (
select u.user_name username, u.lower_email_address email, 
to_date('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(a.attribute_value/1000, 'SECOND') as lastlog
from cwd_user u, cwd_user_attribute a, cwd_directory d
where u.directory_id = d.id
  and d.is_active='T'
  and u.is_active='T'
  and a.attribute_name = 'lastAuthenticationTimestamp'
  and u.id = a.user_id
)
select * from lastlog;

There is an RSS feed for this blog.

#work