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:

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

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