This page last changed on May 07, 2010 by rhartono.

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.

Query to show you the users in reverse order according to the date they last logged in.


OS User management
select  u.username, p.date_val from os_user u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.last.login.date'
order by date_val desc;
LDAP
select u.name, p.date_val from external_entities u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.last.login.date'
order by date_val desc;
Hibernate User Management
select u.name, p.date_val from users u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.last.login.date'
order by date_val desc;


Query to show you the users in reverse order based on their previous login date:


OS User management
select u.username, p.date_val from os_user u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.previous.login.date'
order by date_val desc;
LDAP
select u.name, p.date_val from external_entities u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.previous.login.date'
order by date_val desc;
Hibernate User Management
select u.name, p.date_val from users u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.previous.login.date'
order by date_val desc;


Query that will show you users who have not made any edits or comments since 2007


OS User management
select u.name from os_user u where u.username not in (select creator from content where contenttype in ('BLOGPOST', 'COMMENT', 'PAGE') and year(creationdate) > 2007);
LDAP
select u.name from external_entities u where u.name not in (select creator from content where contenttype in ('BLOGPOST', 'COMMENT', 'PAGE') and year(creationdate) > 2007);
Hibernate User management
select u.name from users u where u.name not in (select creator from content where contenttype in ('BLOGPOST', 'COMMENT', 'PAGE') and year(creationdate) > 2007);


Using JIRA User Management

While Confluence does not store any of the JIRA users information in its database, Confluence still stores the login details in the OS_PROPERTYENTRY table, which we can refer to from the jira user id.

This means we can modify the queries above easily.

Since we are going to use two databases (assuming that they are located on the same server), you need to run this first:

use yourConfluenceDatabaseName;

Then you can execute the queries below, just make sure that you replace YourJIRADATABASE in the queries below with your own JIRA database name.

This query will show you the users in reverse order according to the date they last logged in:

select u.username, p.date_val from YourJIRADATABASE.userbase u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where p.entity_key='confluence.user.last.login.date'
order by p.date_val desc;

If you need to know the users' email addresses so you can contact them, run the following:

select u.username, psfn.propertyvalue as full_name, psem.propertyvalue as email_address, p.date_val as last_login_date
from YourJIRADATABASE.userbase u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
join YourJIRADATABASE.propertyentry pefn on pefn.entity_id = u.id
join YourJIRADATABASE.propertystring psfn on psfn.id = pefn.id
and pefn.entity_name = 'OSUser'
and pefn.property_key = 'fullName'
join YourJIRADATABASE.propertyentry peem on peem.entity_id = u.id
join YourJIRADATABASE.propertystring psem on psem.id = peem.id
and peem.entity_name = 'OSUser'
and peem.property_key = 'email' 
where p.entity_key='confluence.user.last.login.date'
order by p.date_val desc;

This query will show you the same information but based on their previous login:

select u.username, p.date_val from YourJIRADATABASE.userbase u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where p.entity_key='confluence.user.previous.login.date'
order by p.date_val desc;

Here's another query that will show you users who have not made any edits or comments since 2007:

select u.username from YourJIRADATABASE.userbase u where u.username not in (select creator from content where contenttype in ('BLOGPOST', 'COMMENT', 'PAGE') and year(creationdate) > 2007);
Document generated by Confluence on Jul 09, 2010 01:11