Create
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Products
See all
Community resources
Top groups
Explore all groups
Community resources
Learn
Community resources
Events
Community resources

How to query active users in Confluence

Erwin Manuel
Erwin Manuel February 20, 2012

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.

Answer
Watch
Like # people like this
6228 views

5 answers

1 accepted

7 votes
Answer accepted
Andrew Frayling
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.
Get recognized
February 22, 2012

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.

View More Comments
Erwin Manuel
Erwin Manuel February 23, 2012

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

Like
Lia Wieldraaijer
Lia Wieldraaijer February 28, 2020

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

Like
Dhruv Dixit
Dhruv Dixit September 26, 2020

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

 

Like
Reply
6 votes
Michael S
Michael S
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 17, 2012

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;

Reply
2 votes
Andrew Frayling
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.
Get recognized
February 20, 2012

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.

View More Comments
Version 1 Marketplace Solutions
Jason Pepper February 20, 2012

sweet.. that's useful

Like
Erwin Manuel
Erwin Manuel February 21, 2012

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.

Like
Andrew Frayling
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.
Get recognized
February 21, 2012
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
Like
Erwin Manuel
Erwin Manuel February 22, 2012

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

Like
Andrew Frayling
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.
Get recognized
February 22, 2012
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.
Like
Erwin Manuel
Erwin Manuel February 22, 2012

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

Like
Andrew Frayling
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.
Get recognized
February 22, 2012
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
Like
Andrew Frayling
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.
Get recognized
February 22, 2012

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

Like • Om Joshi likes this
Jorge12
Jorge
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.
Get recognized
January 16, 2014

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.

Like
mary.moore
mary.moore December 31, 2019

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>
Like • Diego likes this
Om Joshi
Om Joshi May 9, 2022

is there any ways to get all the user properties?

Like
Diego
Diego April 19, 2023

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>

 

Like
Reply
0 votes
Om Joshi
Om Joshi May 9, 2022

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

Reply
0 votes
Sean Lutner
Sean Lutner March 10, 2014

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?

Reply

Suggest an answer

Log in or Sign up to answer
Confluence
Confluence
TAGS
AUG Leaders

Atlassian Community Events

    See all events