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

[tiendungitd]

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]

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))
 
    <table class="confluenceTable">
        <tr>
            <th class="confluenceTh">User</th>
            <th class="confluenceTh">Last Successful Login Date</th>
        </tr>
        #foreach($username in $usernames)
            #set($user = $userAccessor.getUser($username))
            <tr>
                <td class="confluenceTd">#usernameLink($user.name)</td>
                <td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user).lastSuccessfulLoginDate)</td>
            </tr>
        #end
    </table>
#else
    <p><i>No group with name "$paramgroup" found!</i></p>
#end