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.




Last modified on Mar 5, 2024

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.