Is there a way to bulk delete comments in Confluence?

[Bob Kennedy]

We've been spammed with hundreds of nonsense comments. Does anyone know a way to remove them in bulk?

[Alexey Matveev]

Hello,

You could use the Power Scripts for Confluence add-on:

https://marketplace.atlassian.com/apps/1219507/power-scripts-for-confluence?hosting=server&tab=overview

I've found Power Scripts for Confluence really handy for Confluence admins who want to automate their work and apply bulk actions to Confluence. And it is a free add-on:

You could get all comments for a page and then delete them. Your script would look like this:

number pageId = 123456;
CComment[] comments = getComments(pageId);
for (CComment comment in comments) {
    deleteComment(comment.id);
} 

[Rodrigo Girardi Adami]

Hi Robert,

You may be able to delete the comments from confluence using a delete command in the database, however as far as I know there's no bundled functionality to do this. I could find this feature request, but wasn't implemented yet: https://jira.atlassian.com/browse/CONF-5023

I suggest to try to remove the comments from a page using this Delete SQL command:

delete from content where contenttype = 'COMMENT' and pageid = <page_id>;

Always backup the database before running any SQL alteration or removal command.

Find the page ID above from the page of confluence you want to remove the comments from just by editing the page and looking into the URL. It should have a pageid there.

Cheers,

Rodrigo

[Simon Tost _TNG_]

After just going this route, and because another answer points to this as well, let's expand on this a little.

Of course, the obligatory disclaimer is right:
If you decide to change the database directly, make sure you know what you are doing and have a backup with working restore ready.

It is not completely straight forward to delete entries from the CONTENT table.
Because there are foreign key constraints, that reference it's entries.
There is documentation about Confluence's DB schema over there, including a nice diagram as a starting point for orientation.

My example is from a Confluence 6.15.

Like above, I started with the set of CONTENTID, referencing the comments I wanted to delete.

SELECT CONTENTID
FROM CONTENT
WHERE CONTENTTYPE = 'COMMENT'
  AND PAGEID = $pageid$

Then, I first needed to delete all the dependent data, referencing these like so:

DELETE FROM LIKES WHERE CONTENTID IN (
  SELECT CONTENTID
  FROM CONTENT
  WHERE CONTENTTYPE = 'COMMENT'
    AND PAGEID = $pageid$
);

I found valid entries in:

• LIKES
• CONTENTPROPERTIES
• LINKS
• BODYCONTENT

There are some more table, which plausibly contain no reference to comments (like IMAGEDETAILS, CONTENT_LABELS or CONTENT_PERM_SET) and some where I didn't find any, but may be different from case to case. (NOTIFICATIONS? CONTENT_RELATION?)

Only then was I free to

DELETE
FROM CONTENT
WHERE CONTENTTYPE = 'COMMENT'
  AND PAGEID = $pageid$

---

Additional detail: I had a slightly more complicated criterion: I wanted to delete only comments older than a year.
Which also means, my synatx here is SQL Server specific.
But when starting with a cut-off time, like

WHERE CONTENTTYPE = 'COMMENT'
  AND PAGEID = $pageid$
  AND LASTMODDATE < DATEADD(year, -1, GETDATE())

there is the additonal constraint, that comments might have been created prior to cut-off, but edited afterwards, which creates addtional dependencies via the PREVVER foreign key.

So actually my 'inner subquery' looked like: ("old comments + newer versions of old comments")

SELECT CONTENTID
FROM CONTENT
WHERE CONTENTTYPE = 'COMMENT'
  AND PAGEID = $pageid$
  AND LASTMODDATE < DATEADD(year, -1, GETDATE())
UNION
SELECT CONTENTID
FROM CONTENT
WHERE PREVVER IN (
  SELECT CONTENTID
  FROM CONTENT
  WHERE CONTENTTYPE = 'COMMENT'
    AND PAGEID = $pageid$
    AND LASTMODDATE < DATEADD(year, -1, GETDATE())
)

And my actual criterion became: Comments, which were first posted more than a year ago. But close enough.

Not sure if I was just lucky, and there was at most 1 subsequent edit across the cut-off boundary and this query would need to be written substantially different in the general case.
But I got away with this at least once. O:-)
(Open to improvement suggestions, tough. ;) )

[Simon Tost _TNG_]

After just going this route, and because another answer points to this as well, let's expand on this a little.

Of course, the obligatory disclaimer is right:
If you decide to change the database directly, make sure you know what you are doing and have a backup with working restore ready.

It is not completely straight forward to delete entries from the CONTENT table.
Because there are foreign key constraints, that reference it's entries.
There is documentation about Confluence's DB schema over there, including a nice diagram as a starting point for orientation.

My example is from a Confluence 6.15.

Like above, I started with the set of CONTENTID, referencing the comments I wanted to delete.

SELECT CONTENTID
FROM CONTENT
WHERE CONTENTTYPE = 'COMMENT'
  AND PAGEID = $pageid$

Then, I first needed to delete all the dependent data, referencing these like so:

DELETE FROM LIKES WHERE CONTENTID IN (
  SELECT CONTENTID
  FROM CONTENT
  WHERE CONTENTTYPE = 'COMMENT'
    AND PAGEID = $pageid$
);

I found valid entries in:

• LIKES
• CONTENTPROPERTIES
• LINKS
• BODYCONTENT

There are some more table, which plausibly contain no reference to comments (like IMAGEDETAILS, CONTENT_LABELS or CONTENT_PERM_SET) and some where I didn't find any, but may be different from case to case. (NOTIFICATIONS? CONTENT_RELATION?)

Only then was I free to

DELETE
FROM CONTENT
WHERE CONTENTTYPE = 'COMMENT'
  AND PAGEID = $pageid$

---

Additional detail: I had a slightly more complicated criterion: I wanted to delete only comments older than a year.
Which also means, my synatx here is SQL Server specific.
But when starting with a cut-off time, like

WHERE CONTENTTYPE = 'COMMENT'
  AND PAGEID = $pageid$
  AND LASTMODDATE < DATEADD(year, -1, GETDATE())

there is the additonal constraint, that comments might have been created prior to cut-off, but edited afterwards, which creates addtional dependencies via the PREVVER foreign key.

So actually my 'inner subquery' looked like: ("old comments + newer versions of old comments")

SELECT CONTENTID
FROM CONTENT
WHERE CONTENTTYPE = 'COMMENT'
  AND PAGEID = $pageid$
  AND LASTMODDATE < DATEADD(year, -1, GETDATE())
UNION
SELECT CONTENTID
FROM CONTENT
WHERE PREVVER IN (
  SELECT CONTENTID
  FROM CONTENT
  WHERE CONTENTTYPE = 'COMMENT'
    AND PAGEID = $pageid$
    AND LASTMODDATE < DATEADD(year, -1, GETDATE())
)

And my actual criterion became: Comments, which were first posted more than a year ago. But close enough.

Not sure if I was just lucky, and there was at most 1 subsequent edit across the cut-off boundary and this query would need to be written substantially different in the general case.
But I got away with this at least once. O:-)
(Open to improvement suggestions, tough. ;) Some content.)

[Priyanka Lavania]

Hi Robert,

If you have script runner plugin then you can use built in scripts to bulk delete comments from one or more pages, you can also specify comment age or delete all. Hope this helps.

See this for more details: - https://scriptrunner.adaptavist.com/5.0.5/confluence/builtin-scripts.html

Regards,

Priyanka Lavania