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

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,463,529
Community Members
 
Community Events
176
Community Groups

Is there a way to bulk delete comments in Confluence?

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

3 answers

1 vote

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);
} 

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

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. ;) )

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.)

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

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events