要約

This article describes various methods to determine which user deleted a comment in a specific Jira issue.

環境

Any Jira Server/Data Center version.

ソリューション

Solution 1 (from the UI)

The easiest way to check who deleted a comment from an issue is to open the issue in the Jira UI, and then click on the History tab at the bottom of the issue.

From the history tab, you'll be able to see who deleted a comment in the ticket, as shown in the example below. If you see a comment on the left side and nothing on the right side, then it means that the comment was deleted:

Solution 2 (from the Jira Database)

Alternatively, it is possible to check from the database if a comment was deleted from a specific Jira issue, and who did it, by running the SQL query below, after replacing "ABC" with the project key and "123" with the issue number (the query below will work if the issue key is ABC-123):

SELECT p.pname, p.pkey, i.issuenum, cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR, cg.CREATED, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING
FROM changegroup cg
inner join jiraissue i on cg.issueid = i.id
inner join project p on i.project = p.id
inner join changeitem ci on ci.groupid = cg.id AND ci.FIELDTYPE='jira' AND ci.FIELD='Comment'  AND ci.NEWVALUE IS NULL
inner join app_user au on cg.author = au.user_key
WHERE cg.issueid=(select id from jiraissue where issuenum = 123 and project in (select id from project where pkey = 'ABC')) 
order by 1,3,4;

Example of output (the column lower_user_name will show the username in lower case of the user who deleted the comment):

 pname  |  pkey  | issuenum |  id   | issueid | lower_user_name |    author     |          created           | fieldtype |  field  | oldvalue  | oldstring | newvalue | newstring 
--------+--------+----------+-------+---------+-----------------+---------------+----------------------------+-----------+---------+-----------+-----------+----------+-----------
 BUSNEW | BUSNEW |        4 | 19486 |   18227 | julien          | JIRAUSER10000 | 2022-01-17 11:28:13.346+01 | jira      | Comment | comment 2 |           |          | 
(1 row)

Solution 3 (from the Tomcat Access Logs)

There is also a way to get this information from the Tomcat Access log files, which are located in the directory <JIRA_INSTALLATION_FOLDER>/logs/ of each node in the Jira cluster (when using Jira Data Center). The name of this file has the format access_log.YYYY-MM-DD (1 access log file per day and per node), with YYYY-MM-DD being the date when the access log file was generated.

Whenever an HTTP Request is sent to the Jira application, such request is recorded in the Tomcat Access Logs. If we take the example of the deletion of a Jira comment, the following HTTP request will be recorded in the Tomcat Access log file:

127.0.0.1 688x52410x1 julien [17/Jan/2022:11:28:12 +0100] "GET /jira8130/secure/DeleteComment!default.jspa?id=18227&commentId=16608&inline=true&decorator=dialog&_=1642415281735 HTTP/1.1" 200 844 21 "https://jira8130:8443/jira8130/browse/BUSNEW-4" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36" "1xw691p"

The logs will contain information about:

• the username (julien in the example above)
• the full URL: <JIRA_CONTEXT_PATH>/secure/DeleteComment!default.jspa?id=18227&commentId=16608&inline=true&decorator=dialog&_=1642415281735, which contains:
  • the string DeleteComment!default.jspa
  • the issue id (18227 in the example above)

If you want to use this method, you would basically need to:

• first, get the issue id in the jiraissue table that matches the issue key (for example ABC-123), by using the SQL query below:

  select id from jiraissue where project in (select id from project where pkey = 'ABC') and issuenum = 123;

• then, grep through each access log file from each node by searching for the string "DeleteComment" along with the issue id found in the previous step