Remove Duplicate Jira Issues

Ticketing Systems

If you are upgrading from a version of the Veracode Integration for Jira that is earlier than 3.5.0 and notice duplicate Jira issues during the upgrade, use this procedure to remove duplicate issues.

To remove duplicate issues:
  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, other than Open and To Do, in the Status field 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, contact Veracode Technical Support for assistance.
  2. In Jira, select Issues > Search for issues.
  3. Switch to advanced search and enter the following JQL search query, replacing <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 issues that match the IDs you specified.
  5. Select Tools > Bulk Change to start the bulk change for all issues that the advanced search returned.
  6. In the Choose Issues step, select the Select All checkbox and, then, click Next.


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