users' login activity report

[Anita Duerson]

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.

[Ryan Goodwin]

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!

[Bruce Schlueter]

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

[Julia Erdel]

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

[aviv_cohen]

Hi
Not working for me 
Do any adjustments need to be done for my env?
I've tried to add this USER MACRO as-is to test webpage & it failed

[Daniel Borcherding]

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.

[Bhushan Nagaraj]

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

[Bhushan Nagaraj]

You will need to convert that unix timestamp to datetime