Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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!

2 answers

1 accepted

0 votes
Answer accepted

@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

@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

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!

@RichardA 

 

Hi Richard

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

 

Cheers

Kurt

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!

@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

Like RichardA likes this

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

Thanks !

@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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira

Do you use Jira Cloud with Microsoft Teams?

Hi, Jira users! Do you use Jira alongside Microsoft Teams? We want to hear how you’ve used the power of Jira Cloud and Microsoft Teams (via the Jira Cloud for Microsoft Teams app) to achieve a team...

238 views 1 5
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you