How to get the List of all active & inactive users with last login from confluence DB ?

[Shelly Gez]

How to get the List of all active & inactive users with last login from confluence DB ?

I am using this postgres command, wanted to add the active/inactive table.

Can you assist?

SELECT cu.display_name, cu.email_address,
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
ORDER BY successdate;

Thanks,

Shelly

[Thiago Masutti · Accepted solution]

Hi @Shelly Gez .
I hope you are well.

The following query adds the active column.
It also shows users that never logged in.

SELECT cu.display_name, cu.email_address, cu.active,
cd.directory_name,
li.successdate
FROM logininfo li
right 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
ORDER BY successdate;

Kind regards,
Thiago Masutti

[Shelly Gez]

Thanks @Thiago Masutti  - worked !

[Bob Vasey]

What Confluence DB table would the be run over? 

[Moses Ebrahimi]

any help for Postgresql ? thanks @Thiago Masutti 

[Shelly Gez]

@Bob Vasey - I am using postgresql

[Mahesh Kallepalli]

@Shelly Gez  When I used above select query getting duplicate results. Could you please help me with that.