How to find the last Login for Users

[Roman Joss]

In the following article is descripted how to find the last Login for users.

https://confluence.atlassian.com/crowdkb/essential-sql-queries-for-crowd-696519745.html

But this somhow hase an error. When i open in Confluence (5.9.12) then there are three Dates showed:

Created, Last Updated, Last Login.

With the query shown with cwd_user_attribute.attribute_name = 'lastAuthenticated' there is displayed the 'Last Updated'-Date and not the last Login-Date.

When is this field updated and whats the meaning of 'Last Updated'?

Thanks for any help.
Roman

[Mike Rathwell · Accepted solution]

Roman,

The query I am using for this purpose doesn't look like any of the ones in that example but does seem to present consistent data that I would find on a random sampling of users. Give this one a try. If you also happen to have more than one user directory configured, it will give you that as well.

SELECT u.user_name, d.directory_name, l.successdate
FROM logininfo l
JOIN user_mapping m ON m.user_key = l.username
JOIN cwd_user u ON m.username = u.user_name
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY directory_name, successdate;

[Roman Joss]

Hi Mike

Thank you for your query. Thats exact what i'm looking for. The table logininfo i have not seen.

Great!
Have a nice day and thanks again for your quick help.
Roman