Remove Duplicate Jira Issues

Ticketing Systems

Use this procedure to remove duplicate issues that appeared when you upgraded to the latest version of the Veracode Integration for Jira from a legacy version older than version 3.5.0

About this task

To remove any duplicate issues:

Procedure

  1. Run the following SQL query to obtain a comma-separated list of IDs of the duplicate issues.
    Note: If you have non-terminal values in the Status field other than Open and To Do defined in your Jira workflow, you must update the SQL query to include those additional Status field values.
    • For a MySQL Database:
      SELECT GROUP_CONCAT(ID) FROM jiraissue WHERE ID IN (SELECT ISSUE
      FROM customfieldvalue
      WHERE
          customfieldvalue.CUSTOMFIELD = (SELECT
               ID
          FROM
               customfield
          WHERE
               CUSTOMFIELDTYPEKEY = 'com.veracode.jira.plugin.synchronize.veracode-jira-flaws-synchronizer:veracode-link-field'
               AND CUSTOMFIELDSEARCHERKEY = 'com.veracode.jira.plugin.synchronize.veracode-jira-flaws-synchronizer:veracode-link-searcher')
               AND customfieldvalue.STRINGVALUE IN (SELECT STRINGVALUE FROM customfieldvalue WHERE
               CUSTOMFIELD = (SELECT ID
          FROM
              customfield
          WHERE
              CUSTOMFIELDTYPEKEY = 'com.veracode.jira.plugin.link.VeracodeLink:veracode-link-field'
              AND CUSTOMFIELDSEARCHERKEY = 'com.veracode.jira.plugin.link.VeracodeLink:veracode-link-searcher')))
              AND (issuestatus IN (SELECT ID FROM issuestatus WHERE pname = 'Open' OR pname = 'To Do')) LIMIT 0,500)
              AS jiraissue_temp_2  ON jiraissue_temp_1.ID = jiraissue_temp_2.jiraissue_id;
    • For an Oracle database:
      SELECT LISTAGG(ID, ', ') WITHIN GROUP (ORDER BY ID) jiraissue_ids FROM jiraissue jiraissue_temp_1 INNER JOIN (SELECT jiraissue_id FROM (SELECT ID AS jiraissue_id, ROW_NUMBER() OVER( order by ID asc ) rn  FROM jiraissue WHERE ID IN (SELECT ISSUE FROM customfieldvalue
      WHERE
         customfieldvalue.CUSTOMFIELD = (SELECT
             ID
         FROM
             customfield
         WHERE
             CUSTOMFIELDTYPEKEY = 'com.veracode.jira.plugin.synchronize.veracode-jira-flaws-synchronizer:veracode-link-field'
             AND CUSTOMFIELDSEARCHERKEY = 'com.veracode.jira.plugin.synchronize.veracode-jira-flaws-synchronizer:veracode-link-searcher')
      AND customfieldvalue.STRINGVALUE IN (SELECT STRINGVALUE FROM customfieldvalue WHERE
      CUSTOMFIELD = (SELECT
             ID
         FROM
             customfield
         WHERE
             CUSTOMFIELDTYPEKEY = 'com.veracode.jira.plugin.link.VeracodeLink:veracode-link-field'
             AND CUSTOMFIELDSEARCHERKEY = 'com.veracode.jira.plugin.link.VeracodeLink:veracode-link-searcher')))
             AND (issuestatus IN (SELECT ID FROM issuestatus WHERE pname = 'Open' OR pname = 'To Do'))) where rn BETWEEN 1 AND 500)
             jiraissue_temp_2  ON jiraissue_temp_1.ID = jiraissue_temp_2.jiraissue_id;
    • For a PostgreSQL database:
      SELECT ARRAY_AGG(ID) FROM jiraissue AS jiraissue_temp_1 INNER JOIN (SELECT ID AS jiraissue_id  FROM jiraissue WHERE ID IN (SELECT ISSUE
      FROM customfieldvalue
      WHERE
          customfieldvalue.CUSTOMFIELD = (SELECT
              ID
          FROM
              customfield
          WHERE
              CUSTOMFIELDTYPEKEY = 'com.veracode.jira.plugin.synchronize.veracode-jira-flaws-synchronizer:veracode-link-field'
              AND CUSTOMFIELDSEARCHERKEY = 'com.veracode.jira.plugin.synchronize.veracode-jira-flaws-synchronizer:veracode-link-searcher')
      AND customfieldvalue.STRINGVALUE IN (SELECT STRINGVALUE FROM customfieldvalue WHERE
      CUSTOMFIELD = (SELECT
              ID
          FROM
              customfield
          WHERE
              CUSTOMFIELDTYPEKEY = 'com.veracode.jira.plugin.link.VeracodeLink:veracode-link-field'
              AND CUSTOMFIELDSEARCHERKEY = 'com.veracode.jira.plugin.link.VeracodeLink:veracode-link-searcher')))
              AND (issuestatus IN (SELECT ID FROM issuestatus WHERE pname = 'Open' OR pname = 'To Do')) LIMIT 500)
              AS jiraissue_temp_2  ON jiraissue_temp_1.ID = jiraissue_temp_2.jiraissue_id;
      Note: If you are using a database other than MySQL, Oracle, or PostgreSQL, please contact Veracode Support for assistance.
  2. Log in to Jira and go to Issues > Search for Issues.
  3. Switch to advanced search and enter the following JQL search query, replacing the text <duplicate_ticket_ids> with the output of the SQL query you ran in step 1:
    issuekey in (<duplicate_ticket_ids>)
    If the SQL query output was 10400,10401,10402,10403,10404,10405,10406,10408, then the search query in Jira would be: issuekey in (10400,10401,10402,10403,10404,10405,10406,10408)

    A text area appears, in which you can enter JQL queries.
  4. Run the Issue search to view the list of Jira tickets that match the IDs you specified.

  5. Go to Tools > Bulk Change to start the bulk change for all tickets that the advanced search returned.

  6. In step 1 of the bulk change, click the Select All checkbox, and click Next.

  7. In step 2, select Delete Issues, and click Next.
  8. When prompted, click Confirm.
  9. Repeat steps 1-8 until the SQL query output at step 1 no longer returns any results as there are no more duplicate tickets to delete.
  10. If you suspended your upgrade procedure to remove these duplicate issues, you can now go back to that procedure and continue at step 3.