How to query active users in Confluence

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.

4 answers

1 accepted

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.

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

5 votes

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;

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.

sweet.. that's useful

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.

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

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

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.

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

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

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

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.

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?

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Kesha Thillainayagam
Posted Apr 13, 2018 in Confluence

We want to hear how your non-technical teams are using Confluence!

Hi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...

2,925 views 27 12
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you