nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

Andrew Frayling

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.

All comments

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.

Version 1 Marketplace Solutions

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

Andrew Frayling

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!

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: &lt;your name&gt;
## 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())

&lt;table class="confluenceTable"&gt;
  &lt;tr&gt;
    &lt;th class="confluenceTh"&gt;Count&lt;/th&gt;
    &lt;th class="confluenceTh"&gt;User&lt;/th&gt;
    &lt;th class="confluenceTh"&gt;Last Successful Login&lt;/th&gt;
  &lt;/tr&gt;

#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)
    &lt;tr&gt;
      &lt;td class="confluenceTd"&gt;$count&lt;/td&gt;
      &lt;td class="confluenceTd"&gt;#usernameLink($user.name)&lt;/td&gt;
   
      &lt;td class="confluenceTd"&gt;$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user.name).lastSuccessfulLoginDate)&lt;/td&gt;
    
    &lt;/tr&gt;
  #end
#end
&lt;/table&gt;

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?

Lia

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

Om Joshi_8c083n

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

user