How to find all the comments related to one user in the database

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Why

It's a common need to visualize the comments a particular user created. Many users have been asking about how to retrieve this data at the database, so here we are presenting the resolution (which was only tested in MySQL databases).

Resolution (JIRA 6.0.8 or previous versions)

Run the query below:

SELECT ji.pkey, ja.actionbody
FROM jiraaction ja, jiraissue ji
WHERE ja.issueid = ji.id
AND ja.actiontype = 'comment'
AND ja.author = 'xxx';

(warning) Please remember to change the value of the ja.author variable from 'xxx' to the actual 'username' from the user.


After JIRA 6.1, some changes where made in the database, in that case you will need to run this query:

Run the query below:

SELECT CONCAT(P.pkey,'-',JI.issuenum) as issuekey, JA.actionbody
FROM jiraaction JA
JOIN jiraissue JI ON JA.issueid = JI.id
JOIN project P ON JI.project = P.id
JOIN app_user U ON U.user_key = JA.author
WHERE JA.actiontype = 'comment'
AND U.lower_user_name = 'xxx';

(warning) Please remember to change the value of the U.lower_user_name variable from 'xxx' to the actual 'username' from the user all in lower case.

The feature request for getting this information through JQL is being tracked here :  JRA-1648 - Getting issue details... STATUS .


Last modified on Jun 23, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.