User base audit queries
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
症状
Need to know which users have not logged into Confluence successfully since a certain date
ソリューション
select entity_name, entity_key, date_val from os_propertyentry where entity_key like '%user.last.login.date%' and date_val < (some date value);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.
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')
);
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:
CONF-1556 - Getting issue details... STATUS