Capture Login Count Statistics of Users in Jira Software from DB
要約
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.
環境
Applies to Jira Software and Jira Service Desk installations both server and Data Center.
ソリューション
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.