nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Accepted answers

jing_hwa_cheok

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

All comments

Alex Perez

Hi Bruce,

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

jing_hwa_cheok

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

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

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!

Bruce Boutet

My cwd_user_attribute table is also empty.

Thanks for your suggestions.

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.

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

jira

user-management