How to list a user and last login filter by group in Confluence?

Question

Hi everbody

I want to list all the users and last login date in a specific group. But don't know why the list is not enough. Below is my query, please advice me how to correct it.

If I removed "JOIN logininfo l on um.user_key = l.username", it can list correct amount of users in group "abc".

use confluence;

SELECT u.display_name, g.group_name, u.active, l.SUCCESSDATE
FROM cwd_user u
JOIN cwd_membership m ON u.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
JOIN user_mapping um on u.lower_user_name = um.lower_username
JOIN logininfo l on um.user_key = l.username
WHERE u.active = 'T' And g.lower_group_name = 'abc'
GROUP BY u.lower_user_name

Fabienne Gerhard · Answer

Hi @tiendungitd you can manage that with a user macro right in confluence. Look at the solution made in this case. This works perfect for me! I took the code from @Remo Siegwart: ## @param group:title=Group|type=string|required=true|desc=The group you want to report on #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($group = $userAccessor.getGroup($paramgroup)) #if($group) #set($usernames = $userAccessor.getMemberNames($group)) #foreach($username in $usernames) #set($user = $userAccessor.getUser($username)) #end

User	Last Successful Login Date
#usernameLink($user.name)	$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user).lastSuccessfulLoginDate)

#else

No group with name "$paramgroup" found!

#end