Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Locating Crowd group references throughout Confluence

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? 

1 answer

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 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS
Community showcase
Published in Confluence

👁‍🗨 Confluence Team planning dashboard_Atlympics 🌈

Hi Atlassian's, How is your journey with #Atlympics 2021 so far....excited! Me too, same excitement. Here's my typical team planning and vision dashboard which I used to share to my management and ...

121 views 1 5
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you