How to Retrieve Internal or External Comment in Jira Service Management

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

     

Purpose

The purpose of this article is:

  • To compare the internal and external comments features of Jira Service Management
  • Show how to retrieve internal and external comments in Jira via the database.

Most of the time, this will be useful for reporting purposes.

Comparison

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

  • While 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.

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

    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.



Last modified on Mar 21, 2024

Was this helpful?

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