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

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....

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!

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

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 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...

357 views 20 10
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