Need SQL to remove 5K groups

[Craig Solinski]

Hi,

After running Migration of External Memberships, found over 5 thousand groups had been copied into Confluence database that are not desired/needed/used by confluence in any way.

I've updated the group filter of our LDAP User Directory so these groups will be ignored henceforth, but I wish to delete these groups from Confluence using sql - Oracle 11.x Database.

Can you provide me with appropriate sql - I'm hesitant to create my own to and wish to fully protect data integrity.

Thank you very much,

Craig Solinski

Confluence and Jira Administrator

[Jeremy Largman · Accepted solution]

Hi Craig,

I think you're best off using @Bob Swift's Remote API client rather than SQL. It's the safe operation you're looking for.

Here's the details:

https://studio.plugins.atlassian.com/wiki/display/CSOAP/Confluence+Command+Line+Interface

Basically you'd use the remove group method. You'd need to figure out the names of your groups, then run a script in your language of choice to do the deletion.

[Craig Solinski]

Nice,

Used perl comfluence.pm which uses xml-rpc to connect to confluece and get all groups. Then selected the ones I wanted with perl regex and fed them one at a time into Java Confluence CLI.

my $groups = $wiki->getGroups();

my $directory = '<yourpath>/confluence-cli-2.2.0/lib/confluence-cli-2.2.0.jar ';
my $jargsfirst = '--server <your url> --user xxx --password xxx --action removeGroup --group ';
my $jarglast = "";

foreach my $group (@$groups) {
# for all groups that containing TK_ but it is NOT followed by 'V' or 'D'...
if ($group =~ /TK_[^VD]/) {
print "Found group: ", $group, "\n";
$jarglast = $group;
# call java SOAP command line interpreter to perform the group deletion;
system "java -jar $directory $jargsfirst $jarglast";
$cnt++;
}
}

# I know it was not very efficient but it worked great:)

-----------------------------------

Question: I wanted to simply use perl confluence.pm to do all the work but perl is doing something to token value:

my $token = $wiki->login($url, $user, $password);

Then

$bool = $wiki->removeGroup($token, $group, ""); # fails - complains about token.

print of token looks like: 'Confluence=HASH(0x1008050b0)'

Do you know how I can get confluence token in format that is valid to pass to confluence.pm commands?

[Jeremy Largman]

I don't know that one, sorry. It might be good as it's own top-level question.