Hi Team,
How to fetch user details who have not logged in from past six months in confluence using Oracle data base query.
Can anyone help me on this. Thanks!
@RichardA
Hi Richard
on the first one you will need to adjust the successdate you are looking for, so if you would like to see users having logged in throughout the last 6 months something similar to
<span>SELECT</span> cu<span>.</span>user_name<span>,</span> <span>cd</span><span>.</span>directory_name<span>,</span> li<span>.</span>successdate <span>FROM</span> logininfo li <span>JOIN</span> user_mapping um <span>ON</span> um<span>.</span>user_key <span>=</span> li<span>.</span>username <span>JOIN</span> cwd_user cu <span>ON</span> um<span>.</span>username <span>=</span> cu<span>.</span>user_name <span>JOIN</span> cwd_directory <span>cd</span> <span>ON</span> cu<span>.</span>directory_id <span>=</span> <span>cd</span><span>.</span>id <span>WHERE</span> successdate <span><</span> to_date<span>(</span><span>'01-JUN-2021'</span><span>,</span><span>'DD-MON-YYYY'</span><span>)</span> <span>ORDER</span> <span>BY</span> successdate<span>;</span>
Cheers
Kurt
https://confluence.atlassian.com/confkb/how-to-identify-inactive-users-in-confluence-214335880.html
lists the relevant sql statements like
<span>SELECT</span> cu<span>.</span>user_name<span>,</span> <span>cd</span><span>.</span>directory_name<span>,</span> li<span>.</span>successdate <span>FROM</span> logininfo li <span>JOIN</span> user_mapping um <span>ON</span> um<span>.</span>user_key <span>=</span> li<span>.</span>username <span>JOIN</span> cwd_user cu <span>ON</span> um<span>.</span>username <span>=</span> cu<span>.</span>user_name <span>JOIN</span> cwd_directory <span>cd</span> <span>ON</span> cu<span>.</span>directory_id <span>=</span> <span>cd</span><span>.</span>id <span>WHERE</span> successdate <span><</span> to_date<span>(</span><span>'01-JAN-2016'</span><span>,</span><span>'DD-MON-YYYY'</span><span>)</span> <span>ORDER</span> <span>BY</span> successdate<span>;</span>
Hope that helps
Hi @Kurt Klinner Thanks for the Response.
I have executed the script which you are mentioned. it's giving all the licensed users info who are never logged in.
can you help me to get the list of license users who have not logged in for last 6 months
Thanks!
can you share your SQL statement especially which value you used for the comparison with the successdate column
Hi @Kurt Klinner I have used this below query to get the inactive users list. But its giving all the licensed users info who are never logged in.
This is the query i used:
And we used one more below query for get the same list
<span>SELECT</span> u<span>.</span>lower_user_name <span>FROM</span> cwd_user u <span>JOIN</span> cwd_membership m <span>ON</span> u<span>.</span>id <span>=</span> child_user_id <span>JOIN</span> cwd_group g <span>ON</span> m<span>.</span>parent_id <span>=</span> g<span>.</span>id <span>JOIN</span> SPACEPERMISSIONS sp <span>ON</span> g<span>.</span>group_name <span>=</span> sp<span>.</span>PERMGROUPNAME <span>JOIN</span> cwd_directory <span>d</span> <span>on</span> u<span>.</span>directory_id <span>=</span> <span>d</span><span>.</span>id <span>WHERE</span> sp<span>.</span>PERMTYPE<span>=</span><span>'USECONFLUENCE'</span> <span>AND</span> u<span>.</span>active <span>=</span> <span>'T'</span> <span>AND</span> <span>d</span><span>.</span>active <span>=</span> <span>'T'</span> <span>AND</span> u<span>.</span>lower_user_name <span>NOT</span> <span>IN</span> <span>(</span> <span>SELECT</span> cu<span>.</span>lower_user_name <span>FROM</span> logininfo li <span>JOIN</span> user_mapping um <span>ON</span> um<span>.</span>user_key <span>=</span> li<span>.</span>username <span>JOIN</span> cwd_user cu <span>ON</span> um<span>.</span>username <span>=</span> cu<span>.</span>user_name <span>JOIN</span> cwd_directory <span>cd</span> <span>ON</span> cu<span>.</span>directory_id <span>=</span> <span>cd</span><span>.</span>id <span>)</span> <span>GROUP</span> <span>BY</span> u<span>.</span>lower_user_name<span>,</span> <span>d</span><span>.</span>directory_name <span>ORDER</span> <span>BY</span> <span>d</span><span>.</span>directory_name<span>;</span>
The query giving all the users info who are never logged in and users who have not part of confluence because same Ldap we used for all atlassian tools.
Can you suggest me on this.
It looks like you're new here. Sign in or register to get started.
