MySQL Queries to get list of all users in all groups from CROWD

Hey, We are trying to automate a way where we can get list of all users of all groups, and then remove the deactivated users and get a finalized list. We have 170 groups, so its not easy to do things thru UI.

I was working with bash + querying database. Query to get list of all groups -

#mysql -u$user -p$password --batch -e "use $db; select lower_group_name from cwd_group;"  > /tmp/crowdgroups

And then I wrote a procedure to recursively get all Groups / Childrens of that particular group. This pretty much seems to work for me. I am still searching for loopholes and probability of failure.

One such seems to be the cyclic group thing. Group1 (is member of) Group2, Group2 (is member of) Group3, and then Group3 (is member of) Group1

Any ideas on making this script perfect or other nicer ways to do this? I know there is REST APIs but I did not try them yet.

Procedure is here -
findmembers()
{
#Find all Users in this group and append it to a file
mysql -u$user -p$password --batch -e "use $db;  select lower_child_name from cwd_membership where membership_type='GROUP_USER' AND lower_parent_name='$1';" | tail -n+2 >> /tmp/mygroups
# Check if Group has children groups in it, if so, run recursive for all groups
if [ `mysql -u$user -p$password --batch -e "use $db; select membership_type,group_type,lower_parent_name, lower_child_name from cwd_membership where membership_type='GROUP_GROUP' AND lower_parent_name='$1';"|wc -l` -ne 0 ]
then
#echo "$1 has groups in it"
# Find all groups nested in this group 
mysql -u$user -p$password --batch -e "use $db; select lower_child_name from cwd_membership where membership_type='GROUP_GROUP' AND lower_parent_name='$1';"|tail -n+2 > /tmp/tmp_$1
# Go through each group and find all the children in it. 
while read tgroups
do
findmembers $tgroups
done
fi
}


3 answers

1 accepted

This widget could not be displayed.

Well this is gonna be unanswered.

This widget could not be displayed.

This is my MySQL query to find all groups for each directory and their members:

SELECT CD.directory_name AS 'directory',
       CG.group_name AS 'group',
       (
        SELECT GROUP_CONCAT(U.user_name ORDER BY U.user_name ASC SEPARATOR ',')
        FROM cwd_membership M 
          LEFT JOIN cwd_user U ON M.child_user_id = U.id
          LEFT JOIN cwd_group G ON M.parent_id = G.id
        WHERE G.id = CG.id
        ) AS 'members'
FROM cwd_group CG
  LEFT JOIN cwd_directory CD ON CG.directory_id = CD.id
-- WHERE CD.directory_name = 'Confluence Internal Directory'
ORDER BY CD.directory_name, CG.group_name;
This widget could not be displayed.

error

 

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted 3 hours ago in Teamwork

What teamwork quotes inspire you?

Hey everyone! My name is Natalie and I'm an editor of the Atlassian Blog and I've got a question for you: What's your favorite quote about teamwork?  We've compiled a list here, along with...

14 views 0 4
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you