Can some help me come up with a SQL command to display the users located in the local groups of bitbucket?
I have the following command that I used for Confluence:
SELECT cu.id as user_id, cu.user_name, cg.group_name, cd.directory_name
FROM cwd_user cu
INNER JOIN cwd_membership cm ON (cu.id = cm.child_user_id)
INNER JOIN cwd_group cg ON (cm.parent_id = cg.id)
INNER JOIN cwd_directory cd ON (cu.directory_id = cd.id)
WHERE cg.local='T' ORDER BY cg.group_name
But this command doesn't seem to work in bitbucket. Is the database schema that different between the two products? Would someone have a bitbucket equivalent command to produce the data?
Thanks for any assistance on this.
I dug down into the database columns and tried to compare the differences between Confluence and Bitbucket and found the the difference was Confluence used 'local' in the cwd_group table and Bitbucket uses 'is_local' in the cwd_group table.
Once I plugged in the 'is_local' into the WHERE clause, I got the results I was expecting.
Hi @warecm43
Maybe you're looking for something like this?
SELECT u.user_name,
g.group_name,
g.id AS "group_id",
d.directory_name,
d.id AS "directory_id"
FROM cwd_membership m
INNER JOIN cwd_user u ON m.child_id = u.id
INNER JOIN cwd_group g ON m.parent_id = g.id
INNER JOIN cwd_directory d ON g.directory_id = d.id
ORDER BY d.directory_name, u.user_name, g.group_name;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I found that query and it gets me all the groups including the Active Directory groups. I was looking for a WHERE clause that limits the search to the local bitbucket groups.
The "WHERE cg.local='T'" in my original post does this in Confluence, but when I add the equivalent to my BitBucket command (i.e. WHERE g.local='T'), I receive a g.local does not exist.
I was hoping there might be something in the BitBucket database that would equate to the cg.local field in the Confluence DB.
I'm sure the command would work fine once I know what field holds the information to tell if the group is a local group or not.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.