nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

All comments

Bhushan Nagaraj2

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 Nagaraj2

You will need to convert that unix timestamp to datetime

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



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

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

           #end 
           
           &lt;/td&gt;
        &lt;/tr&gt;
    #end
&lt;/table&gt;
#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

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.

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!

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

confluence-administration