This page last changed on Jul 20, 2011 by mryall.

If you want to disable inactive users and prevent them from being counted by Confluence license count it is possible to find out by running queries against your database.

This is particularly useful if you have numerous users.

Note: the queries are the same regardless of which user management system you're using in Confluence 3.5 or later. See the old version of this document for the various queries needed with legacy user management systems.

List users who are inactive
select * from cwd_user
where active = 'F';
List users by last login date
select entity_name, date_val from OS_PROPERTYENTRY
where entity_key = 'confluence.user.last.login.date'
and entity_name like 'CWD_%'
order by date_val;
List users by previous login date

The "previous" login date is the one before the user's last login.

select entity_name, date_val from OS_PROPERTYENTRY
where entity_key = 'confluence.user.previous.login.date'
and entity_name like 'CWD_%'
order by date_val;
Active users who have not created any content since 2007
select u.user_name from cwd_user u
where u.user_name not in (
    select creator from content
    where contenttype in ('BLOGPOST', 'COMMENT', 'PAGE')
    and creationdate > '2007-01-01')
and u.active = 'T';
Document generated by Confluence on Sep 19, 2011 02:50