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

This article only applies to Atlassian's server products. Learn more about the differences between cloud and server.

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 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 Apr 30, 2019

Was this helpful?

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