How to query active users in Confluence

[Erwin Manuel]

Hi,

We would like to find out which of our users is still active because we plan to modify the format of email address, we are using LDAP authentication. I found scripts from http://confluence.atlassian.com/display/CONF34/How+Do+I+Identify+Inactive+Users+in+Confluence. However, when I test the scripts, it seems that it is not sufficient to show the active users.

For example, when I use the LDAP/Crowd script:

select u.name, p.date_val from external_entities u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.last.login.date'
order by date_val desc;

It displays that my last login was in 2011, but that is not correct.

Can you recommend a script that allows us to query the active users? Thanks in advance.

[Andrew Frayling · Accepted solution]

Scratch that last update, try this instead:

## 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 it didn't like the test for null values on 3.4.6.

Sorry about the last update, copy and pasting between VMs and I copied the wrong version.

Andrew.

[Erwin Manuel]

This works, thanks! Buy lastly is it possible to sort the rows from the recent-to-oldest last login? Thanks again!

[Lia Wieldraaijer]

This query no longer works in Confluence 7.0, any ideas what changed?

[Dhruv Dixit]

Any idea how to make this macro work for Confluence 7.0.1? Looks like the below does not work anymore. @Andrew Frayling 

$loginManager.getLoginInfo($user.name).lastSuccessfulLoginDate

[Michael S]

For those interested, I made a quick alteration to give the user count:

## Macro title: Last Login
## Macro has a body: N
## Body processing: Selected body processing option
## Output: Selected output option
##
## Developed by: Andrew Frayling
## Modified by: Michael Seager [Atlassian Support]
## 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">Count</th>
    <th class="confluenceTh">User</th>
    <th class="confluenceTh">Last Successful Login</th>
  </tr>

#set($count = 0)

#foreach($user in $users)
  ## list the last login date of users who can view the current space
  #if ($permissionHelper.canView($user, $space))
    #set($count = $count + 1)
    <tr>
      <td class="confluenceTd">$count</td>
      <td class="confluenceTd">#usernameLink($user.name)</td>
   
      <td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user.name).lastSuccessfulLoginDate)</td>
    
    </tr>
  #end
#end
</table>

[Andrew Frayling]

H Erwin,

I wrote a user macro to do this recently, details available at - http://blog.networkedcollaboration.com/2012/02/11/confluence-last-login-user-macro/

My user macro just tests for the last login of users with access to a particular space, but if you remove the following if statement:

#if ($permissionHelper.canView($user, $space))

so the modified macro is:

## 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
  
    <tr>
      <td class="confluenceTd">#usernameLink($user.name)</td>
    #if (!$loginManager.getLoginInfo($user).lastSuccessfulLoginDate)
      <td class="confluenceTd" style="background-color:#ff0000"><strong>NEVER</strong></td>
    #else
      <td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user).lastSuccessfulLoginDate)</td>
    #end
    </tr>
  
#end
</table>

it should work for everyone who has access to Confluence.

Hope that helps,

Andrew.

[Jason Pepper]

sweet.. that's useful

[Erwin Manuel]

Hi Andrew,

Unfortunately, this is not sufficient for our case. We are using LDAP authentication and the macro might not be picking up correct info. I tried this out and it displayed last login = 'NEVER' for all users.

[Andrew Frayling]

Hi Erwin, sorry it didn't work. I'll do some testing against LDAP as I would expect it to work. Out of interest if you go to Browse -> Confluence Admin -> Manage Users and lookup your username what does it show for your last login there? Andrew

[Erwin Manuel]

When I go to View User it shows the correct last login date. This is also correct for all the other users.

[Andrew Frayling]

Hi Erwin, which version of Confluence are you using? Just noticed the link you posted is from the 3.4 docs. I tested against LDAP yesterday and it worked, but that was on 4.1.5 and haven't had chance to try on older versions yet.

[Erwin Manuel]

Yes we are using 3.4.6 but we are planning to upgrade in the near future.

[Andrew Frayling]

Ok, that probably explains things as user management changed in 3.5+ I'll find some time to see if the macro can be re-written for 3.4

[Andrew Frayling]

Hi Erwin,

Does the following work:

## 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>
    #if (!$loginManager.getLoginInfo($user).lastSuccessfulLoginDate)
      <td class="confluenceTd" style="background-color:#ff0000"><strong>NEVER</strong></td>
    #else
      <td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user.name).lastSuccessfulLoginDate)</td>
    #end
    </tr>
  #end
#end
</table>

The only change is the line:

<td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user.name).lastSuccessfulLoginDate)</td>

which used to be:

<td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user).lastSuccessfulLoginDate)</td>

Tested against 3.4.6 against Apache DS LDAP.

Andrew

[Jorge]

Hi, I don't know if its due to our server being slow or what. But I got this

Error: Rendering this content exceeded the timeout of 120 seconds.

Is ther a way to get a lighter of this macro? We have about 900 users today.

[mary.moore]

When we were under 500 users the below macro worked perfectly for me; however, now that we are over 900 users - the system times out (the same as Jorge above). Is there a better way to get all Confluence users name and last login for audit purposes? 

## 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
  
    <tr>
      <td class="confluenceTd">#usernameLink($user.name)</td>
    #if (!$loginManager.getLoginInfo($user).lastSuccessfulLoginDate)
      <td class="confluenceTd" style="background-color:#ff0000"><strong>NEVER</strong></td>
    #else
      <td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user).lastSuccessfulLoginDate)</td>
    #end
    </tr>
  
#end
</table>

[Om Joshi]

is there any ways to get all the user properties?

[Diego]

Hi @mary.moore / @Jorge12

I ran into the same problem, as my instance had more than 1000 users. I modified the shared macro a bit, so that the export can be done by range. In my case I exported the first 500 users by putting the values 0 at the beginning of the range and 500 at the end of the range, then 500 at the beginning of the range and 1000 at the end of the range.

I hope it will be useful for more users😁

## 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: Diego Zepeda
## Macro to display the last login date of users with access to confluence
## @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">Username</th>
<th class="confluenceTh">Email</th>
<th class="confluenceTh">Last Successful Login</th>
</tr>


#set($count = 0)
#set($startIndex = 0) ##Range start (default 0)
#set($endIndex = 10) ##End of range


#foreach($user in $users)
#if ($permissionHelper.canView($user, $space))
#if($count >= $startIndex && $count < $endIndex)
<tr>
<td class="confluenceTd">$user.fullName</td>
<td class="confluenceTd">$user.name</td>
<td class="confluenceTd">$user.email</td>
<td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user.name).lastSuccessfulLoginDate)</td>
</tr>
#end
#set($count = $count + 1)
#end
#end
</table>

[Om Joshi]

is there any way to fetch the complete list of users with groups, active, inactive  etc. including all the fields.

[Sean Lutner]

Is it possible to display the email address on record for the user when using the macro provided above? I've tried guessing at what the field/value would be but $email and $email.address and other variations don't seem to work. Any suggestions?