users' login activity report

Is there a way to generate a report that would tell me the last time a user logged in to Confluence ? It will take too much time for me to individually open each user's login box to check the last log in date. For instance, it would be nice to be able to export all user details into an Excel spreadsheet and then manipulate user information to help monitor usage and make decisions about removing non-users. Thanks.

4 answers

This widget could not be displayed.

Hi Anita,

we built us a user macro to list the user logins:

## @param group:title=Group|type=string|required=true|desc=Group Filter

#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($crowdService = $containerContext.getComponent('crowdService'))
#set($t1=$content.currentDate.time)

#set($group = $userAccessor.getGroup($paramgroup))

#if($group)
    #set($usernames = $userAccessor.getMemberNames($group))



<table class="confluenceTable" width="660px">
    <tr>
        <th class="confluenceTh " width="220px">Name</th>
        <th class="confluenceTh"  width="110px">Create Date</th>
        <th class="confluenceTh" width="110px">Last Login</th>
        <th class="confluenceTh" width="110px" style="text-align:center;">Activated</th>
        <th class="confluenceTh" width="110px" style="text-align:center;">Days</th>
    </tr>
    #foreach($username in $usernames)
        #set($t3="9999")
        #set($user = $userAccessor.getUser($username))
        #set($crowdUser = $crowdService.getUser($user.name))
        
        <tr>
           <td class="confluenceTd">#usernameLink($user.name)</td>
           <td class="confluenceTd" style="text-align:center;">$action.dateFormatter.formatGivenString('dd.MM.yyyy', $crowdUser.createdDate)</td>
           <td class="confluenceTd" style="text-align:center;">$action.dateFormatter.formatGivenString('dd.MM.yyyy',$loginManager.getLoginInfo($user).lastSuccessfulLoginDate)</td>
          <td class="confluenceTd" style="text-align:center;">
           #if($userAccessor.isDeactivated($user))
                    <ac:macro ac:name="status">
                    <ac:parameter ac:name="colour">Red</ac:parameter>
                   <ac:parameter ac:name="title">No</ac:parameter>
                   </ac:macro>
           #else
                   <ac:macro ac:name="status">
                   <ac:parameter ac:name="colour">Green</ac:parameter>
                   <ac:parameter ac:name="title">Yes</ac:parameter>
                   </ac:macro>
                  #set($totalactiveusers = $totalactiveusers + 1)
            #end
                

            </td>
         
          #if($loginManager.getLoginInfo($user).lastSuccessfulLoginDate)
                #set($t3= $content.currentDate.time -  $loginManager.getLoginInfo($user).lastSuccessfulLoginDate.time)
                #set($t3=$t3/1000/24/60/60)
           #end
           <td class="confluenceTd" style="text-align:center;">
           #if($t3 < 7) 
                 <ac:macro ac:name="status">
                 <ac:parameter ac:name="colour">Green</ac:parameter>
                 <ac:parameter ac:name="title">$t3</ac:parameter>
                 </ac:macro>
           #elseif($t3 < 14) 
                 <ac:macro ac:name="status">
                 <ac:parameter ac:name="colour">Yellow</ac:parameter>
                 <ac:parameter ac:name="title">$t3</ac:parameter>
                 </ac:macro>
           #elseif($t3 < 9999) 
                 <ac:macro ac:name="status">
                 <ac:parameter ac:name="colour">Red</ac:parameter>
                 <ac:parameter ac:name="title">$t3</ac:parameter>
                 </ac:macro>
           #else 
                 <ac:macro ac:name="status">
                 <ac:parameter ac:name="colour">Magenta</ac:parameter>
                 <ac:parameter ac:name="title">$t3</ac:parameter>
                 </ac:macro>

           #end 
           
           </td>
        </tr>
    #end
</table>
#end

This shoes us the login and can be filtered for different groups. It shows red or green icons depending on the last login. Perhaps that helps you.

Regards, Bruce

THAAAAAANK YOOOU!!! That was exactly what we were searching for....

This widget could not be displayed.

Hi Anita,

I wrote some queries you can use to find out this information. Please see this page:

https://confluence.atlassian.com/display/CONFKB/User+base+audit+queries

Hope this helps!

This widget could not be displayed.

Hi Anita,

There is nothing available in the Confluence UI but you can get it from the database/

select u.display_name as "Name",
c.attribute_value as "UNIX Time Stamp"
from dbo.cwd_user_attribute c
inner join dbo.cwd_user u
ON c.user_id=u.id
where c.attribute_name='lastAuthenticated'

You might find some pointers here too

https://answers.atlassian.com/questions/19460/user-and-content-statistics-metrics-of-confluence-activity

You will need to convert that unix timestamp to datetime

This widget could not be displayed.

Anita,

All of the solutions posted thus far are great!

I did want to take a moment to direct you to our document about user enabling user access logging.

https://confluence.atlassian.com/display/CONFKB/How+to+Enable+User+Access+Logging

This is a bit more a brute force way of doing things and requires some scripting on your part to tease out the information you are interested in.

We also have an outstanding feature request for this behavior.

https://jira.atlassian.com/browse/CONF-23034

I am going to go ahead and attach this post to this ticket to let our devs know that our community is still looking for this feature.

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Published yesterday in Confluence

Add-on evaluation with confluence templates

Atlassian market place contains number of Apps/Addons which improves the capability of out of the box Atlassian products. It is good to follow a plugin evaluation process before install add-ons. So t...

44 views 6 3
Read article

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