Capture Login Count Statistics of Users in Jira Software from DB
Summary
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.
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:
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
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.
Last modified on Mar 5, 2024
Powered by Confluence and Scroll Viewport.