Purpose

This article compares the internal and external comment properties that Jira Service Management uses and shows how to retrieve these comments in Jira via the database.

Most of the time, this will be useful for reporting or identifying issues against your application. 

Comparison

When you add a comment and click "Comment internally" you will see an "INTERNAL" label beside the comment from the Jira agent view :

• If you share a comment with the customer, it's present on the "Comments" tab without any label, which is meant for external comments:

• Also, if you "View customer request" on the customer portal, you can see only the comments that have been shared with the customer and not the internal ones :

Solution

While there is currently no way to search for comments in the front end, we do have a Suggestion in the backlog to address this feature request:

JSDSERVER-4286 - Getting issue details... STATUS

As a workaround, our suggestion is to run a few database queries.

• To find all internal comments, run the following SQL query:

  select project.pkey, jiraissue.issuenum, jiraissue.summary, jiraaction.actionbody
      from jiraaction
      inner join entity_property on jiraaction.id=entity_property.entity_id
      inner join jiraissue on jiraaction.issueid=jiraissue.id
      inner join project on project.id=jiraissue.project
  	where entity_property.entity_name='sd.comment.property' and 
   (entity_property.json_value='{"internal":true}' or entity_property.json_value='{"internal":"true"}');

• To find all external comments, run the following SQL query:

  select project.pkey, jiraissue.issuenum, jiraissue.summary, jiraaction.actionbody
      from jiraaction
      inner join jiraissue on jiraaction.issueid=jiraissue.id
  	inner join project on project.id=jiraissue.project
  	where jiraaction.id not in ( select entity_id from entity_property where jiraaction.id=entity_property.entity_id
   and entity_property.entity_name='sd.comment.property' and 
   (entity_property.json_value='{"internal":true}' or entity_property.json_value='{"internal":"true"}'));

• The sample result:

   pkey | issuenum |          summary          | actionbody
  ------+----------+---------------------------+------------
   DESK |        1 | Create a customer request | hello
   DESK |        2 | TEST1                     | MOGA
  (2 rows)

Internal or external attachments

Attachments don't have a flag to be marked as Internal or external.

If they are placed on an internal comment, they are considered internal, so if we'd like to query internal or external attachments, we need to add a clause with a RegEx to catch the attachments by the format of the filename with the following changes.

Below is a query to catch attachments with the internal tag with regular expressions added:

select project.pkey, jiraissue.issuenum, jiraissue.summary, jiraaction.actionbody
    from jiraaction
    inner join entity_property on jiraaction.id=entity_property.entity_id
    inner join jiraissue on jiraaction.issueid=jiraissue.id
    inner join project on project.id=jiraissue.project
	where entity_property.entity_name='sd.comment.property' 
and (entity_property.json_value='{"internal":true}' or entity_property.json_value='{"internal":"true"}')
	and jiraaction.actionbody ~ '^!|[.*\.[A-Za-z]+]|!|\|.*$';

The above example will find all the internal comments containing attachments.

Internal or external comments made by Apps

Jira Service Management uses the property key "sd.public.comment." Still, for compatibility with some plugins, it also uses "sd.allow.public.comment" to distinguish whether a comment is internal or public.

If you wish to check that a comment has a property set against the older property key, please use the following SQL query:

select project.pkey, jiraissue.issuenum, jiraissue.summary, jiraaction.actionbody
    from jiraaction
    inner join entity_property on jiraaction.id=entity_property.entity_id
    inner join jiraissue on jiraaction.issueid=jiraissue.id
    inner join project on project.id=jiraissue.project
	where entity_property.property_key='sd.allow.public.comment' and 
 (entity_property.json_value='{"allow":false}' or entity_property.json_value='{"internal":"true"}');