User base audit queries

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

Need to know which users have not logged into Confluence successfully since a certain date

Resolution

For Confluence versions 3.5 - 4.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
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
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 +
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')
        );

 

(info) You will need to specify the date range, group name, and directory name to run this query.

 

(info) 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:

CONF-1556 - Getting issue details... STATUS

Last modified on Feb 19, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.