User base audit queries

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

症状

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

ソリューション

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 - 課題詳細を取得中... ステータス

最終更新日: 2016 年 2 月 19 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.