Redacting PHI from Jira

Connect to Jira database (find most updated info here)

Have affected ticket id e.g. BQMS-2378

Plug ticket id in last line of SQL to return list of ticket changes:

SELECT
    p.pkey||'-'||i.issuenum                 as "KEY",
    ci.field,
    cg.created                              as Modified,
    dbms_lob.substr(ci.oldstring, 4000, 1)  as OLD_String,
    dbms_lob.substr(ci.newstring, 4000, 1)  as NEW_String,
    dbms_lob.substr(ci.oldvalue, 4000, 1)   as OLD_Value,
    dbms_lob.substr(ci.newvalue, 4000, 1)   as NEW_Value,
    ci.id                                   as Change_Item_Id
FROM changeitem ci
JOIN changegroup cg                         on ci.groupid = cg.id
JOIN jiraissue i                            on cg.issueid = i.id
JOIN project p                              on i.project = p.id
WHERE p.pkey||'-'||I.issuenum in ('BQMS-2378')

Most commonly, the item to be changed is the description.

Find the record that has the PHI in the OLD_String or OLD_Value field, and copy that Change_Item_Id in the following UPDATE statement.

Also, set the text that should appear in the ticket. Conventionally I’ve just been entering [redacted] over the PHI along with my initials and date, but the important thing is the PHI is cleared.

UPDATE changeitem
SET OldString = 'Set Text Here'
where changeitem.id = 1819534

In SQL Developer, commit statement by pressing F11 or clicking commit button.