I am running JIRA 6.1.7 with an Oracle backend.
I'm almost at the limit when it comes to licenses, so I need to clear up some users who've not logged in for a long time. This can be done on the GUI but it's very slow and long.
Is there a way to do this on the DB? I ran the following:
SELECT d.directory_name,
u.user_name,
TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN globalpermissionentry gp ON m.parent_name = gp.GROUP_ID
WHERE gp.PERMISSION IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
) m ON m.child_name = u.user_name
LEFT JOIN (
SELECT *
FROM cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc;
I get an error that the table doesn't exist.
Can someone tell me which query will work? Note that I only want to see active users, as the inactive ones are not counting towards my licence limit. So how do I find (for example) users who are 1. active but who 2. have not logged into the system in the past six months?
Thanks.
