Admin SQL queries

Extract list of users and groups

One line for each group

 SELECT user.Name, user.EMAIL, user.IsActive, usergroup.Groupname, usergroup.LDAPNAME
 FROM user
 INNER JOIN usergroupmember ON usergroupmember.UserID = user.UserID
 INNER JOIN usergroup ON usergroupmember.GroupID = usergroup.GroupID
 ORDER BY 1

One line for each user

 SELECT user.Name, user.EMAIL, user.IsActive, GROUP_CONCAT(usergroup.Groupname) AS ListOfGroups
 FROM user
 INNER JOIN usergroupmember ON usergroupmember.UserID = user.UserID
 INNER JOIN usergroup ON usergroupmember.GroupID = usergroup.GroupID
 GROUP BY usergroup.GroupID
 ORDER BY 1