Query to identify users who are never logged-in or not logged-in since 1 year or more.

DTS Infra Admin March 10, 2019

We have lot of users who are not logging since  6 months or 1 year, need to identify them as a part of cleaning activity, but i found below query from atlassian community which is giving invalid data while checking in front end. So let us know any query to find  valid data for never logged in or not logged in since 1 year .

 

Query: 

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

-------------------------

2 answers

0 votes
Ed Letifov _TechTime - New Zealand_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 16, 2019

You can use our plugin User Management for Jira which is available for Datacenter.

It will give you ability to export to CSV, but most importantly to see all the users who haven't logged in for a while directly via our Bulk User actions.

If you don't want to buy, it's fine to use it on Eval license as a one-off but please leave a review - this really helps our sales!

Our support is 24x7 - so feel free to reach out if you have any questions!

0 votes
Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 10, 2019

Have you compared the results with what is shown under User Management. It would be interesting to see if they are different. FWIW, I use the export and then use excel to establish my ‘deactivate’ list. It allows me to change the “not logged in since” value.

DTS Infra Admin May 15, 2019

There isn't any direct option to export userlist from jira-user-management page (/admin/user/UserBrowser.jspa). how do you export this to excel ?

Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 15, 2019

In cloud there is an export function (button) on the Users page under User management. I would think that server does as well. I found this article in Atlassian’s documentation - how-to-export-users-to-csv-from-a-jira-application.

In cloud...

96A4FB40-F621-42C1-ADCF-3AB63797F3BA.jpeg

DTS Infra Admin May 15, 2019

Ah ! I use the data-centre version of jira software and it doesnt give me an export option. It makes sense to have it. Don't know why Atlassian left it out of this product. it is only for cloud at the moment.

Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 15, 2019

only a guess but as DC is for very large installations it might be a challenge to download many thousands of user. You might want to reach out to Atlassian Support.

Suggest an answer

Log in or Sign up to answer