Number of members in each "global permission" group

Björn Eitmann July 12, 2021

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

2 answers

Suggest an answer

Log in or Sign up to answer
0 votes
Peter-Dave Sheehan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 13, 2021

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 July 13, 2021

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

 

Screenshot 2021-07-14 083924.jpg

Peter-Dave Sheehan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 14, 2021

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
Like Björn Eitmann likes this
Björn Eitmann July 14, 2021

THIS IS GREAT!!!

Thank you so much!!!

0 votes
Peter-Dave Sheehan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 12, 2021

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 July 13, 2021

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 13, 2021

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. 

TAGS
AUG Leaders

Atlassian Community Events