Number of members in each "global permission" group

[Björn Eitmann]

Hi*

We are working with about 40 different global permission groups.

I need to know, how many users/members are in each group.

Maybe someone already have a sql query or script for ScriptRunner?!

Thanks!

Björn

[Peter-Dave Sheehan]

Let's try this again ...

Here is a scriptrunner script that will output the global permissions, which groups are assigned to them and how many users each group has:

import com.atlassian.sal.api.component.ComponentLocator
import com.atlassian.confluence.security.SpacePermissionManager
import com.atlassian.user.GroupManager

def spm = ComponentLocator.getComponent(SpacePermissionManager)
def groupManager = ComponentLocator.getComponent(GroupManager)

def globalPermissionTypes = spm.globalPermissions.collect{it.type}.unique()
globalPermissionTypes.collect{type->
   def groups = spm.getGlobalPermissions(type).collect{
   it.group
}
   log.info "$type is granted to groups $groups"
   groups
}.flatten().findAll().unique().each{groupName ->
   def group = groupManager.getGroup(groupName)
   def count = groupManager.getMemberNames(group).size()
   log.info "Group $group has $count users"
}

 If you prefer sql query, this should work too (in mysql):

Select
   p.PERMGROUPNAME,
   Group_Concat(Distinct p.PERMTYPE) As Permissions,
   Count(Distinct u.lower_user_name) As `Count of Users`
From
   SPACEPERMISSIONS p Inner Join
   cwd_group g On g.lower_group_name = p.PERMGROUPNAME Inner Join
   cwd_membership m On m.parent_id = g.id Inner Join
   cwd_user u On m.child_user_id = u.id
Where
   p.SPACEID Is Null
Group By
   p.PERMGROUPNAME

It might need adjustments if you have multiple user directories and some duplication between them.

In both cases, the counts currently include both active and inactive users.

[Björn Eitmann]

Works, but output are only the group names without a count. These both rows shows an error. Do i have to customize them?

[Peter-Dave Sheehan]

The error is not a breaking one. "Static Type Checking" are warning that something COULD be wrong and the editor can't be sure. In this case the execution is not impacted.

We can coherce the type to get the error to go away like this:

 def group = groupManager.getGroup(groupName as String)

Have you looked in the Logs tab? That's where this was configured to be outputted.

If you want to have some sort of formatted output in the result tab, we can add some fancy markup:

import groovy.xml.MarkupBuilder
import com.atlassian.sal.api.component.ComponentLocator
import com.atlassian.confluence.security.SpacePermissionManager
import com.atlassian.user.GroupManager

def spm = ComponentLocator.getComponent(SpacePermissionManager)
def groupManager = ComponentLocator.getComponent(GroupManager)
def writer = new StringWriter()
def html = new MarkupBuilder(writer)
def permissions

html.table(class:'aui'){
   tr{th{html.mkp.yield('Permission')}; th{html.mkp.yield('Groups')}}
   def globalPermissionTypes = spm.globalPermissions.collect{it.type}.unique()
   permissions = globalPermissionTypes.collect{type->
      def groups = spm.getGlobalPermissions(type).collect{
         it.group
      }
      tr{td{html.mkp.yield(type)};td{html.mkp.yield(groups.join(', '))}}
      log.info "$type is granted to groups $groups"
      groups
   }
}
html.br()
html.table(class:'aui'){
   tr{th{html.mkp.yield('Group')}; th{html.mkp.yield('User Count')}}
   permissions.flatten().findAll().unique().each{groupName ->
      def group = groupManager.getGroup(groupName as String)
      def count = groupManager.getMemberNames(group).size()
      tr{td{html.mkp.yield(groupName)};td{html.mkp.yield(count)}}
      log.info "Group $group has $count users"
   }
}
return writer

[Björn Eitmann]

THIS IS GREAT!!!

Thank you so much!!!

[Peter-Dave Sheehan]

Something like this or derived from it should get you what you want (or close):

import com.atlassian.jira.component.ComponentAccessor

ComponentAccessor.globalPermissionManager.getAllGlobalPermissions().collect{gp->
def groups = ComponentAccessor.globalPermissionManager.getGroupNamesWithPermission(gp.globalPermissionKey)
   log.info "$gp.globalPermissionKey has groups $groups"
   groups
}.flatten().unique().each{ group->
   def count = ComponentAccessor.groupManager.getUsersInGroupCount(group as String)
   log.info "Group $group has $count users"
}

[Björn Eitmann]

This does not work in Confluence 7.4.8

The script could not be compiled: <pre>org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script19.groovy: 1: unable to resolve class com.atlassian.jira.component.ComponentAccessor @ line 1, column 1. import com.atlassian.jira.component.ComponentAccessor ^ 1 error </pre>.

[Peter-Dave Sheehan]

Oops. Sorry I completely missed the tags. 
i should be able to get you a similar script for confluence tomorrow (I’m on my phone right now). If not I’ll share a sql query.