Last login date in Confluence

Nariman Riahi February 21, 2012

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

3 answers

1 vote
Ket Tintinger October 11, 2012

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

1 vote
Remo Siegwart
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 21, 2012

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

Nariman Riahi February 21, 2012

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.

Remo Siegwart
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 21, 2012

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/

Nariman Riahi February 22, 2012

I did all the steps and get NEVER for all the users. Something is not right. It even shows I never logged in.

Remo Siegwart
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 22, 2012

What kind of user management are you using? Not sure whether this works with external user management.

Andrew Frayling
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 22, 2012
Which version of Confluence are you using? I did a quick test on Confluence 4,1.5 against LDAP and it worked, but I'm not sure if it works against 3.x versions of Confluence as user management changed to Crowd in 3.5+
Andrew Frayling
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 22, 2012

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.

Nariman Riahi February 22, 2012

I have 3.5.3 and the new Macro worked perfectly. Thanks

0 votes
Kjell Lauren September 19, 2012

I tried this on 3.5.17 and it only lists ~20 users out of 1500, what might be the problem?

Andrew Frayling
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 20, 2012
Hi, See a modified version posted in the comments of a post at http://blog.networkedcollaboration.com/2012/02/11/confluence-last-login-user-macro/ and an alternative at https://answers.atlassian.com/questions/33385/macro-that-produces-a-list-of-users-last-login-date?page=1 Crowd and/or external LDAP can affect how the macros return details on users. Hope one of those help? Andrew
Kjell Lauren September 20, 2012

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

Nelson Jimenez Manio April 9, 2017

Does all the macros mentioned would work in 
Confluence v5.8.x ?

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events