Locating Crowd group references throughout Confluence

[Rudy Torres]

Hello All,

We are discontinuing the use of Crowd for authentication and will be using Active Directory (LDAP).  We've created corresponding groups in AD to the main groups that exist in Crowd and will be assigning global permissions to those groups for access.  There are many other groups that exist in Crowd that we need to map to the new group names or change the Crowd referenced group names to the new AD group names.  Many of the Crowd group names exist throughout Confluence in Spaces and other application areas.  Is there a way to locate all the group names and change them to the new AD grp names?  For example,  Crowd group 'Middleware' is now named 'GRP_IT_Middleware' in AD.  We would like to find all references to 'Middleware' and change them to 'GRP_IT_Middleware' wherever they may reside in Confluence.  Are there any SQL queries available that could help us identify existence and replace? 

[Hack Vogel]

Hi Rudy,

The following SQL (SQLServer 2016) is what was used to locate all occurrences of groups in Confluence. The results were exported to a spreadsheet to map the Crowd group name to the Active Directory group name. The directory ID for Crowd and Active Directory can be obtained from the cwd_directory table.

There were also instances of a group name being in both the Crowd and Active Directory so conditions had to be added to target the appropriate directory.

Use 'confluence database name in your instance'
Go

---- list all groups in spaces
SELECT distinct sp.PERMGROUPNAME as GroupName
,s.SPACENAME
,s.SPACETYPE
,s.SPACESTATUS
,'Spaces' as EntityType
, 'n/a' as PageName
, cg.directory_id
FROM SPACES s
join SPACEPERMISSIONS sp on sp.PERMGROUPNAME > '' and sp.SPACEID = s.SPACEID
join cwd_group cg on cg.lower_group_name = sp.PERMGROUPNAME AND cg.active = 'T' AND cg.directory_id in ( [refer to cwd_directory table to obtain directory ID] )
where s.SPACETYPE = 'global' AND s.SPACESTATUS = 'CURRENT'

UNION ALL

---- list groups on restricted pages
SELECT distinct cp.GROUPNAME as GroupName
, s.SPACENAME
,s.SPACETYPE
,s.SPACESTATUS
,'Restricted Pages' as EntityType
,c.TITLE as PageName
, cg.directory_id
FROM CONTENT_PERM cp
JOIN cwd_group cg on cg.group_name > '' and cg.group_name = cp.GROUPNAME AND cg.active = 'T' AND cg.directory_id in ([refer to cwd_directory table to obtain directory ID]) 
INNER JOIN CONTENT_PERM_SET cps ON cp.CPS_ID = cps.ID
INNER JOIN CONTENT c ON c.CONTENTID = cps.CONTENT_ID
INNER JOIN SPACES s ON c.SPACEID = s.SPACEID AND s.SPACETYPE = 'global' AND s.SPACESTATUS = 'CURRENT'
WHERE cp.GROUPNAME > ''
and cp.GROUPNAME not in (select AD.group_name from cwd_group AD where AD.directory_id =[refer to cwd_directory table to obtain directory ID])

UNION ALL

--- list all groups in spacepermissions
SELECT distinct sp.PERMGROUPNAME as GroupName
,s.SPACENAME
,s.SPACETYPE
,s.SPACESTATUS
--, NULL as perm_type
,'Permissions' as EntityType
,'Permissions Tab' as PageName
, cg.directory_id
FROM SPACEPERMISSIONS sp
join [dbo].SPACES s on s.SPACEID = sp.SPACEID AND s.SPACETYPE = 'global' AND s.SPACESTATUS = 'CURRENT'
join [dbo].cwd_group cg on cg.lower_group_name = sp.PERMGROUPNAME AND cg.active = 'T' AND cg.directory_id in ([refer to cwd_directory table to obtain directory ID]])
where sp.PERMGROUPNAME > '' and sp.PERMGROUPNAME not in (select AD.group_name from cwd_group AD where AD.directory_id = [refer to cwd_directory table to obtain directory ID])
ORDER by s.SPACENAME, EntityType, PageName