User base audit queries
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
Need to know which users have not logged into Confluence successfully since a certain date
Resolution
For Confluence versions 3.5 - 4.1 :
1
select entity_name, entity_key, date_val from os_propertyentry where entity_key like '%user.last.login.date%' and date_val < (some date value);User Audit Query for Confluence 4.2
1
select username, successdate from logininfo where successdate < (some date value);These queries specify a date value to evaluate the last login success date in the login info table as well as users that belong the confluence-users group and are part of the confluence internal directory. Modify these values to fit your environment.
Confluence 4.2 - Confluence 5.1.5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select l.username, l.successdate
from logininfo l
join cwd_user u on l.username = u.user_name
join cwd_membership m on u.id = m.child_user_id
where l.successdate < '<insert-date/range-here>'
and m.parent_id =
(select g.id
from cwd_group g
where g.lower_group_name ='confluence-users'
and g.directory_id=
(select d.id
from cwd_directory d
where d.lower_directory_name = 'confluence internal directory')
);
Confluence 5.2.3 +
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select u.lower_user_name, l.successdate
from logininfo l
join user_mapping um on l.username = um.user_key
join cwd_user u on u.lower_user_name = um.lower_username
join cwd_membership m on u.id = m.child_user_id
where l.successdate < '<insert-date/range-here>'
and m.parent_id =
(select g.id
from cwd_group g
where g.lower_group_name ='confluence-users'
and g.directory_id=
(select d.id
from cwd_directory d
where d.lower_directory_name = 'confluence internal directory')
);ℹ️ You will need to specify the date range, group name, and directory name to run this query.
ℹ️ These queries can also be modified to identify users that have not had a successful login to Confluence. Generation of a list can be used to action upon the identified users, most likely by scripts using the Command Line Interface associated user functions.
For previous versions of Confluence, please refer to this link: CONFSERVER-1556 - Display last login time in UserBrowser
Was this helpful?