How to generate a list of Jira users for a very specific criteria?

[sanan sanan]

Hi,

My requirement is that I want to fetch a list of all user of our jira setup as per following criteria

1) The users who are active

3) Who have ever logged in (means it should exclude the users who have never logged in)

We use oracle

In the result I want 

Full name, user name, email id, last login date, 

I know I can this list from the Users feature in users mangement. But the issue is that it can list only 100 users maximum per page and we have more 30 pages. 

Any sql query or any type of solution, please?

Any help will be appreciated. 

[Andy Heinzer]

There is a KB for Users who have never logged into Jira.  It would be relatively simple to modify this SQL query to include all users that HAVE logged into to look like this:

select user_name from cwd_user where user_name in 
(SELECT cwd_user.user_name
FROM cwd_user, cwd_user_attributes
WHERE cwd_user_attributes.user_id = cwd_user.id
AND cwd_user_attributes.attribute_name = 'lastAuthenticated');

In turn this should only return users have have successfully logged into Jira at least once.

We can then add to that the an additional query to find the active users, such as:

select user_name from cwd_user where user_name in 
(SELECT cwd_user.user_name
FROM cwd_user, cwd_user_attributes
WHERE cwd_user_attributes.user_id = cwd_user.id
AND cwd_user_attributes.attribute_name = 'lastAuthenticated')
and active = '1';

Granted, I have not tested this with Oracle, but this works with my Postgres instance.  I suspect the syntax is very similar.

Please let me know the results.

Cheers,

Andy

[sanan sanan]

Hi Andy,

sorry for the delayed reply. and, thanks for providing the tips.

Actually, I found another, weird but funny way around. 

I listed all the users, copied them to the excel sheet and filtered the columns on users who have never logged in. And that is all I wanted. 

Unfortunately, since I dont have direct access to the database. firing sql commands would have been a bit problematic for me, you see.

Regards

sanan

[Andy Heinzer]

Thanks for letting us know your work-around.  I agree, that without SQL access to the Jira database, this task would be rather difficult to accomplish.