Deteremine Active Users

[Bruce Boutet]

We are trying to determine all our Active Jira and Confluence users. It would be nice if we could filter users on Login Details and/or export the results to Excel. This would simplify managing our users, until that is an option I was wondering how are others determining Active/Inactive users?

[jing_hwa_cheok · Accepted solution]

Hi Bruce,

You can run the query below in your Confluence database to list all the active user in Confluence.

SELECT
o.list_index,
d.active,
d.id as DirectoryID,
d.directory_name,
d.directory_type,
u.id as UserID,
u.user_name
FROM
cwd_user u
JOIN cwd_directory d
ON
u.directory_id = d.id
JOIN cwd_app_dir_mapping o
ON
d.id=o.directory_id
ORDER BY 2 DESC, 1 ASC;

For the sql query to list all the active user in JIRA, you can follow this KB https://confluence.atlassian.com/display/JIRAKB/How+to+Get+a+List+of+Active+Users+Counting+Towards+the+JIRA+License.

Hope this helps.

Cheers,
Jing Hwa

[Bruce Boutet]

Our Jira (4.2.3) instance does not have the cwd_user table and in Confluence (3.4.9) it is an empty table.

Thanks for your help!

[jing_hwa_cheok]

Hi Bruce,

Sorry, I din't know that you're using Confluence 3.4. You should run this two query to find the internal/external user with the group associations for Confluence.

select e.name, g.groupname from external_entities e
left join external_members m on e.id=m.extentityid
left join groups g on m.groupid=g.id
order by e.name;
 
select u.name, g.groupname from users u
left join local_members m on u.id=m.userid
left join groups g on m.groupid=g.id
order by u.name;

I'm not well verse with JIRA but you can find the list of available user by going to the Administrator >> User then filter users under the group jira-users. This should list the active user.

[Renjith Pillai]

The last login time is there in the database tables. Also I guess marketplace has plugins which can automatically deactivate users.

[Bruce Boutet]

My cwd_user_attribute table is also empty.

Thanks for your suggestions.

[Alex Perez]

Hi Bruce,

take a look ... https://answers.atlassian.com/questions/188174/users-logged-in-in-past-12-months-jira

[Bruce Boutet]

I used the FireFox TableTools plugin to filter all inactive users and copy them as tab delimited text to report on.

Thanks everyone for all your suggestions.