How to find all the comments related to one user in the database
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. 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 from the database, so here we are presenting the resolution (which was only tested in MySQL databases).
Environment
Jira Data Center on any version from 8.0.0
Solution
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';
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 : JRASERVER-1648 - Allow search on issues I have commented.