Capture Login Count Statistics of Users in Jira Software from DB (Server and Data Center)
Platform Notice: Cloud and Data Center - This article applies equally to both cloud 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
Summary
The solution presented in this article is provided as-is and is not guaranteed to work reliably on your version of Jira. If you use it, we recommend you test it to ensure it produces the expected output. Atlassian does not support this solution.
DB table cwd_user_attributes in the Jira database stores user-level information like Total Login count, Total Failed Login count, Last Login time, etc. It can be used to extract daily/monthly login statistics from the database.
Environment
Applies to Jira Software and Jira Service Desk installations both server and Data Center.
Solution
Total Count of logins to Jira in a Day or in a Timeframe
The below query shows the total number of logins for all users in the Jira instance so far.
1
2
3
4
5
jiradb=# select sum(attribute_value::int) from cwd_user_attributes where attribute_name='login.count' ;
sum
------
2915
(1 row)We can schedule to run this query at a fixed time each day and then can measure the increase in the value to find the total logins during that duration.
Count of Unique users who logged in after a given date
Below query finds the users who have logged into Jira after a given date:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
jiradb=# select cu.user_name,to_timestamp((attribute_value::bigint)/1000)::date from cwd_user_attributes cua join cwd_user cu on cua.user_id=cu.id and attribute_name='login.lastLoginMillis' and to_timestamp((attribute_value::bigint)/1000)::date > '2021-11-01';
user_name | to_timestamp
-----------+--------------
test12 | 2021-11-30
test1 | 2021-11-29
test2 | 2021-11-29
test3 | 2021-11-30
abcd | 2021-11-30
sp4 | 2021-11-30
(6 rows)
jiradb=# select count(*) from cwd_user_attributes cua join cwd_user cu on cua.user_id=cu.id and attribute_name='login.lastLoginMillis' and to_timestamp((attribute_value::bigint)/1000)::date > '2021-11-01';
count
-------
6
(1 row)
Note: Replace the date "2021-11-01" with the required date in both the queries.
Login Count for Each User
1
2
3
4
5
6
jiradb=# SELECT user_id, display_name, updated_date last_login, attribute_value login_count FROM cwd_user a, cwd_user_attributes b where attribute_name = 'login.count' and a.id = b.user_id;
user_id | display_name | last_login | login_count
---------+--------------+----------------------------+-------------
10000 | Test | 2023-12-15 14:54:35.723-06 | 15
10001 | Test1 | 2023-12-15 12:32:54.882-09 | 6
(2 rows)The above queries were designed for Postgres DB and you may need to be modified as per DB type.
Was this helpful?