Is there a way I can tell the last login date of all users in one screen? I know I can see it by clicking on the user name. I just need to look at 500 users and see who has not logged in for a while.
Thanks
NR
If you have access to the repository database, you can use the following query. You can modify the bucket sizes how you wish. We are using LDAP for external authentication, so I needed add the 'EXT_' on the join.
SELECT
case when days_since_last_login = 0 then '0'
when days_since_last_login < 7 then 'Last Week'
when days_since_last_login < 30 then 'Last 30 Days'
when days_since_last_login < 90 then 'Last 90 Days'
when days_since_last_login < 180 then 'Last 180 Days'
when days_since_last_login < 360 then 'Last 360 Days'
when days_since_last_login < 999999 then 'Over 360 Days'
else 'No Login Data' end Last_Login_Grouping
, entity_group
, count(*) User_Qty
FROM
EXTERNAL_ENTITIES exm
LEFT JOIN
(
SELECT
entity_name
, substr(entity_name, 0,3) entity_group
, entity_key
, date_val
, case when date_val IS NULL then 999999 else trunc(sysdate - date_val) end days_since_last_login
, 1 user_qty
FROM
os_propertyentry
WHERE
entity_key = 'confluence.user.last.login.date'
) osp
ON
concat('EXT_', EXM.name) = osp.entity_name
GROUP BY
case when days_since_last_login = 0 then '0'
when days_since_last_login < 7 then 'Last Week'
when days_since_last_login < 30 then 'Last 30 Days'
when days_since_last_login < 90 then 'Last 90 Days'
when days_since_last_login < 180 then 'Last 180 Days'
when days_since_last_login < 360 then 'Last 360 Days'
when days_since_last_login < 999999 then 'Over 360 Days'
else 'No Login Data' end
, entity_group
I wrote a user macro to display all users and their last successful login date here: https://answers.atlassian.com/questions/33385/macro-that-produces-a-list-of-users-last-login-date
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for your quick reply. how do I use this ? I only know how to use the existing Macros when I am writing a wiki page.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You need to put the code in a User Macro. Andrew Frayling wrote a great blogpost about this here: http://blog.networkedcollaboration.com/2012/02/11/confluence-last-login-user-macro/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I did all the steps and get NEVER for all the users. Something is not right. It even shows I never logged in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
What kind of user management are you using? Not sure whether this works with external user management.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try this version:
## Macro title: Last Login ## Macro has a body: N ## Body processing: Selected body processing option ## Output: Selected output option ## ## Developed by: Andrew Frayling ## Date created: 11/02/2012 ## Installed by: <your name> ## Macro to display the last login date of users who have access to the current space ## @noparams #set($containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager')) #set($getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null)) #set($containerManager = $getInstanceMethod.invoke(null,null)) #set($containerContext = $containerManager.containerContext) #set($loginManager = $containerContext.getComponent('loginManager')) #set($users = $userAccessor.getUsers()) <table class="confluenceTable"> <tr> <th class="confluenceTh">User</th> <th class="confluenceTh">Last Successful Login</th> </tr> #foreach($user in $users) ## list the last login date of users who can view the current space #if ($permissionHelper.canView($user, $space)) <tr> <td class="confluenceTd">#usernameLink($user.name)</td> <td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user.name).lastSuccessfulLoginDate)</td> </tr> #end #end </table>
Seems versions prior to 3.5 didn't like the test for null values and printed "NEVER" for everyone. Removing the test seems to have fixed it.
Andrew.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I tried this on 3.5.17 and it only lists ~20 users out of 1500, what might be the problem?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Thanks for the links!
The one on http://blog.networkedcollaboration.com/2012/02/11/confluence-last-login-user-macro/ results in 20 users out of 1500 with NEVER as last login date.
The one on https://answers.atlassian.com/questions/37178/last-login-date-in-confluence results in 20 users out of 1500 with the correct date in the last login date.
The ones from https://answers.atlassian.com/questions/33385/macro-that-produces-a-list-of-users-last-login-date?page=1 results in all users but no date for last login. Date for account created is correct.
We are using LDAP, so maybe this could be a problem?
So something strange that I can't figure out...any good hints and suggestions that would make it work? OR how to troubleshoot and fix the problem?
Really need something to ease up the cleaning old users.
///Kjell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Does all the macros mentioned would work in
Confluence v5.8.x ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.