How to pull the overall number of logins this month for JIRA? (PostgreSQL)

[zapzap47]

How to pull the overall number of logins this month for JIRA? (PostgreSQL)

I need this for auditing and cant seem to get a number :) I will eventually need to do this for Confluence and Bitbucket, any help would be appreciated.

[Radek Dostál]

This requirement is folly and misguided. What is it trying to achieve?

Login counts are arbitrary and can be inflated without the user's knowledge, due to app links or due to basic auth in integrations.

Such a number surely cannot represent any meaningful data, because it:

* cannot be accurate

* can be inflated artificially

* can be inflated without the user's knowledge

* can differ due to session timeout

* can differ due to downtime

[zapzap47]

This is something that I require even if it can be artificially inflated by people spam logging in

[Radek Dostál]

Ok great but then it doesn't matter because the data you require is practically and theoretically useless.

And no, you won't get it with a single query, because such a number is not tracked anywhere, you would need to compare the table data between time ranges. I can assure you, nobody has written anything to do it, because the requirement is pointless.

[Aditya Verma]

Hi @zapzap47 ,

Welcome to the community!

Below is an Oracle DB query for Jira. Please edit it for Postgres if required. This will return you all users with their last login details.

SELECT d.directory_name, u.id, u.directory_id, u.user_name, u.active, u.created_date, u.updated_date,
u.first_name, u.last_name, u.display_name, u.email_address,
TO_DATE('19700101','yyyymmdd') + ((a.attribute_value/1000)/24/60/60) as last_login_date
FROM cwd_user u
LEFT JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) m ON m.child_name = u.user_name
LEFT JOIN (
SELECT *
FROM cwd_user_attributes
WHERE attribute_name = 'login.lastLoginMillis'
) a ON a.user_id = u.ID
LEFT JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc;

Also,please note that last login date is not always correct if the user checks "remember me" per https://jira.atlassian.com/browse/JRASERVER-60508

Thanks,

Aditya

[zapzap47]

Hi, thanks for the reply but I am after the overall count of logins for the month not just on a user basis.

[Aditya Verma]

This will provide you list of users in descending order of their last login. You can export the data into an excel and easily filter out a month data.

Thanks,
Aditya

[zapzap47]

I am after the whole count not just their singular logins to the total logins regardless of user so if a user logs in 30 times that month I need that :)

[Aditya Verma]

So, you need total number of logins per user and for all users in last one month.
This KB article can provide some direction in that case.

Thanks,
Aditya

[zapzap47]

Thanks for the KB, I've tried the first "select sum(attribute_value::int) from cwd_user_attributes where attribute_name='login.count'"

But I am getting nothing come back from the database, any thoughts?