How to retrieve the information those people who haven't logged in from past six months

[RichardA]

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!

[Kurt Klinner · Accepted solution]

@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 

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 < to_date('01-JUN-2021','DD-MON-YYYY')
ORDER BY successdate;

 Cheers

Kurt

[Kurt Klinner]

@RichardA 

Hi Richard

https://confluence.atlassian.com/confkb/how-to-identify-inactive-users-in-confluence-214335880.html

lists the relevant sql statements like

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 < to_date('01-JAN-2016','DD-MON-YYYY')
ORDER BY successdate;

Hope that helps

Cheers

Kurt

[RichardA]

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!

[Kurt Klinner]

@RichardA 

Hi Richard

can you share your SQL statement especially which value you used for the comparison with the successdate column 

Cheers

Kurt

[RichardA]

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:

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 < to_date('01-JAN-2016','DD-MON-YYYY')
ORDER BY successdate;

And we used one more below query for get the same list 

SELECT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m ON u.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
JOIN cwd_directory d on u.directory_id = d.id
WHERE sp.PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T' AND u.lower_user_name NOT IN (
 SELECT cu.lower_user_name
 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
)
GROUP BY u.lower_user_name, d.directory_name
ORDER BY d.directory_name;

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.

Thanks!

[Kurt Klinner]

@RichardA 

Hi Richard

sorry for the late reply.

You will need to adjust the date 

01-JAN-2016

to the one that fits your requirements

Let me know if that helps

Cheers

Kurt

[RichardA]

Hi @Kurt Klinner Thanks for the reply, Its working as expected :) after changing the data value.

Thanks !

[Kurt Klinner]

@RichardA 

Glad that i could support, would be great if you could mark that question as answered so that other with a similar challenge can see that there is a solution

Happy Holidays

Kurt