how many times users have logged into jira for a project in past 3 months?

[Yampalla Srinivas]

Could anyone help me to retrieve a report from jira datbase , How many times users for a perticular project logged into jira in past 3 months?

[Yampalla Srinivas]

Hi  @Tuncay Senturk 

could you say query for logged in users list  belongs to perticular project from past 3 months ?

[Tuncay Senturk]

As I mentioned, there is no such historical data, AFAIK.

[Yampalla Srinivas]

number of Logged in times data for a user is not possible. 

but

Based on last logged in time we can pull the data for logged in users from past 3 months. 

[Tuncay Senturk]

Ohh sorry, I misunderstood your question. I thought you want to get number of login counts per user within 3 months. 

So yes this table can give you the users which logged in within e months.

[Tuncay Senturk]

It depends on your database but in Postgresql you can type an SQL as below

select distinct u.user_name
from cwd_user u, cwd_user_attributes ua
where ua.attribute_name = 'login.lastLoginMillis' 
and cast(ua.attribute_value as float) > EXTRACT(EPOCH FROM TIMESTAMP '2019-03-01 00:00:00') * 1000

 You can either type the desired min date or just get the value as millis to be relative for the current date.

I hope it helps

[Yampalla Srinivas]

@Tuncay Senturk 

Thank you

[Tuncay Senturk]

Hi @Yampalla Srinivas 

Could you please accept an answer (if it was helpful) so that the post will be marked as resolved?

Thanks

[Tuncay Senturk]

Hi @Yampalla Srinivas 

As far as I know, you can't directly get that historical data.

Jira stores below attributes in cwd_user_attributes table.

"lastAuthenticated"
"login.currentFailedCount"
"login.lastLoginMillis"
"login.previousLoginMillis"
"login.count"

[RichardA]

Hi @Tuncay Senturk ,

Could you please suggest how to get user license details using jira user groups from DB side.

and what is the peak time when users are logging into jira using DB query.

[Tuncay Senturk]

Hi @RichardA 

You can check this link: https://confluence.atlassian.com/jirakb/how-to-audit-license-usage-and-user-activity-in-jira-1005789475.html 

[RichardA]

Hi @Tuncay Senturk , 

We need information to get number of users logged on particular time interval in jira/Confluence application.

for example today 27-09-2021 how many users are logged in in between 9am to 5pm .

we are using Oracle DB.

Pls suggest.

Thanks