Find confluence-users that haven't logged in for six months

Question

Hello

I'm trying to find Confluence users, in the group confluence-users, that have not logged in for X days/months and are not disabled.

With my limited SQL I've got this far.

select cu.USER_NAME, cd.DIRECTORY_NAME, li.SUCCESSDATE
from LOGININFO li 
join user_mapping um on um.user_key = li.USERNAME 
join CWD_USER cu on um.username = cu.USER_NAME 
join CWD_DIRECTORY cd on cu.DIRECTORY_ID = cd.ID 
where SUCCESSDATE < '2019-01-01' AND cu.ACTIVE = 'T'
order by SUCCESSDATE DESC;

But I don't know how to add to the search, to include just users who are in confluence-users group.

Can anyone help with this?

Thank you

Mark

Shannon S · Answer

Hello Mark,

If the purpose of restricting your search to the confluence-users group is to ensure the users are licensed in Confluence, this step isn't necessary. The query is already only showing licensed Confluence users.

At any rate, the cwd_membership table is what links the relationship between the users and groups. Have a look at the Confluence Data Model page if you have not already.

Let me know if that helps!

Regards,

Shannon

Mark Thornhill · Answer

Hello Shannon

My SQL already looks for active users, but I need to also find users in the group confluence-users, as they are the ones I need to remove.

I don't understand how the cwd_user and cwd_group are linked, as I think these tables have the info I need to look for, to be able to find the confluence-users.

Mark

Shannon S · Answer

Hello Mark,

Thank you for contacting us about this. I'm not certain myself, but I think perhaps you could use this article to help guide you. Have you seen it?

* How to identify inactive users in Confluence

It doesn't specifically refer to that group but identifies all licensed Confluence users who have not been active.

Regards,

Shannon