How can I retrieve a list of LICENSED users that have not logged in for a year or more?

[Rehnuma Noor]

My organization's Confluence instance (v6.15.4) uses LDAP for authentication. Currently we have 4 user groups in Confluence that fall under the Licensed Users category, including a read-only group. As an admin, I receive requests to allow access for specific users and I do this by adding them to one of these groups ("confluence-users"). When we near the license limit my team and I review our record-keeping spreadsheet that tells us the last login of users. If they have not logged in for 6 months or more we remove them from the user group so that they no longer consume a license.

This hasn't been very efficient because my team and I have been receiving an influx of requests due to project demand. I wanted to see if there was a way to check this information in the backend so that we don't have to keep going back to the spreadsheet.

Thank you,

Rehnuma

[JP _AC Bielefeld Leader_]

Hi,

I use a user macro to generate a nice little report...

## @param group:title=Group|type=string|required=true|desc=Usergroup|default=confluence-users

#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($now=$content.currentDate.time)
#set($group = $userAccessor.getGroup($paramgroup))
#if($group)
#set($usernames = $userAccessor.getMemberNames($group))
#end
#set($totalactiveusers = 0)
<table class="confluenceTable" width="660px">
<tr>
<th class="confluenceTh">Name</th>
<th class="confluenceTh">Last Loging</th> 
<th class="confluenceTh" style="text-align:center;">Days/Status</th>
</tr>
#foreach($username in $usernames)
#set($t3="9999")
#set($user = $userAccessor.getUser($username))
<tr>
<td class="confluenceTd">#usernameLink($user.name)</td>
<td class="confluenceTd" style="text-align:center;">$action.dateFormatter.formatGivenString('yyyy-MM-dd HH:mm',$loginManager.getLoginInfo($user).lastSuccessfulLoginDate)</td>
#if($loginManager.getLoginInfo($user).lastSuccessfulLoginDate)
#set($t3= $now - $loginManager.getLoginInfo($user).lastSuccessfulLoginDate.time)
#set($t3= $t3/1000/24/60/60)
#end
<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">Locked</ac:parameter>
</ac:macro>
#else
#set($totalactiveusers = $totalactiveusers + 1)
#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 < 31) 
<ac:macro ac:name="status">
<ac:parameter ac:name="colour">Blue</ac:parameter>
<ac:parameter ac:name="title">$t3</ac:parameter>
</ac:macro>
#elseif($t3 < 180) 
<ac:macro ac:name="status">
<ac:parameter ac:name="colour">Yellow</ac:parameter>
<ac:parameter ac:name="title">$t3</ac:parameter>
</ac:macro>
#else 
<ac:macro ac:name="status">
<ac:parameter ac:name="colour">Red</ac:parameter>
<ac:parameter ac:name="title">$t3</ac:parameter>
</ac:macro>
#end
#end
</td>
</tr>
#end
</table>
<p>Active users is group <b>$paramgroup</b>: $totalactiveusers</p>

As formatting gets f$%# even when I try using a code block, you might like to copy & paste it to notepad or something similar before creating the macro in Confluence.

Example Screenshot (I removed the user names...).

Hope this helps.

Best

JP

[Rehnuma Noor]

Hi JP,

Thanks for your quick response.

I've never used macros with Confluence. Will it change anything on the UI? Will it retrieve information from all of the 4 groups we have set up?

[JP _AC Bielefeld Leader_]

Hi,

no, it won’t change the UI. The macro generates a standard Confluence table and generates other macros for the status display. This macro needs a group name parameter (default to confluence-users). I would create a page with multiple instances of the macro with different group names. 
You can modify the macro to your needs. 
User macros can be very powerful. Give them a try.

Best

JP

[Dominic Lagger]

Great user macro! Thanks for sharing!

Regards, Dominic

[Rehnuma Noor]

So I tried adding the macro but for some reason it won't show up in the macro browser. I'm not sure if I'm setting this up correctly.

[JimmyVanAU]

There's also this app:

Manage Inactive Users for Confluence 

that will manage inactive users in Confluence for you :)

[Rehnuma Noor]

Thanks, Jimmy. As I've mentioned above to Ivan we want to avoid installing plug-ins at the moment. We have a protocol to get them reviewed by our security team before installing, which tends to be a lengthy process.

Thanks for suggesting!

[Ivan Lima]

@Rehnuma Noor , I've answered a similar question to this one at

• https://community.atlassian.com/t5/Confluence-questions/How-to-check-for-users-who-never-logged-into-Confluence/qaq-p/1257819

I would take a look at the post I mentioned. Perhaps, it will also help you here.

Cheers.

[Rehnuma Noor]

Thanks, Ivan. I see that the recommendation is to use "User Management for Confluence" however, we want to avoid plug-ins to see of there's a workaround like a query or macro, as JP suggested. Appreciate the feedback! 

[Ivan Lima]

You're very welcome 😊