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;
-------------------------
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!
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.