Confluence User Audit

Question

I am butting up against my user limit and we would like to evaluate who has access but hasn't access Confluence in X weeks/months.

Can someone supply the query to show last login (which can be found in your profile)?

Unfortunately I am hitting brick walls trying to figure out this query/joins.

Thank you in advance!

Jonathan Smith · Answer

.

Jonathan Smith · Answer

@Ivan Lima

Thank you for that link. Unfortunately we are only returning 1 row which is not accurate.

Note: We use active directory server instead of 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 < '2020-10-01 08:00:00.000' --24 hour format
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 = 'active directory server')
);

Ivan Lima · Answer

Hi @Jonathan Smith, did you have a chance to look into the User base audit queries confkb and the related content on this page? I know it's not a direct answer to your question but it should help you to achieve what you want here.

Cheers,
IL.