PHI removal script issues. cant get it to work for the life of me .

RBlor
Mega Guru

‎08-30-2024 12:20 PM

I would love any help to get this to work. 
I have the following script but i dont see it removing and redacting any data in a subprod instance as a background script: 

I recieve the following error: 

[0:00:00.012] Compacting large row block (file.write: sys_audit 10000 rows 160000 saveSize)
[0:00:00.012] Compacting large row block (file.write: sys_audit 10000 rows 160000 saveSize)
[0:00:00.012] Compacting large row block (file.write: sys_audit 10000 rows 160000 saveSize)
[0:00:00.013] Compacting large row block (file.write: sys_audit 10000 rows 160000 saveSize)
[0:00:00.012] Compacting large row block (file.write: sys_audit 10000 rows 160000 saveSize)
[0:00:00.276] id: vituitydev_1[glide.6 (connpid=1014989)] for: DBQuery#loadResultSet[sys_audit: tablename=incident^fieldnameINdescription,short_description,comments,work_notes]
Time: 0:00:00.812 id: vituitydev_1[glide.6] primary_hash=-157876246 (connpid=1014989) for: SELECT sys_audit0.`sys_id` FROM sys_audit sys_audit0  WHERE sys_audit0.`tablename` = 'incident' AND sys_audit0.`fieldname` IN ('description' , 'short_description' , 'comments' , 'work_notes') /* vituitydev015, gs:BE7B4DDE93D89610B3F6B6AA6ABA1066, tx:ab25b15e9310d610b3f6b6aa6aba1024, hash:-157876246 */ 
Javascript compiler exception: JavaAdapter requires at least one argument. (null.null.script; line 86) in:
(function() {
    var tableName = 'incident';
    var fieldsToCheck = ['description', 'short_description', 'comments', 'work_notes']; 

    var piiPatterns = [
        // Social Security Numbers (SSNs) - handling spaces and hyphens
        /\b\d{3}[ -]?\d{2}[ -]?\d{4}\b/i, 

        // Credit Card Numbers - handling spaces and hyphens
        /\b(?:4[0-9]{3}[ -]?[0-9]{4}[ -]?[0-9]{4}[ -]?[0-9]{4}|5[1-5][0-9]{2}[ -]?[0-9]{4}[ -]?[0-9]{4}[ -]?[0-9]{4}|3[47][0-9]{2}[ -]?[0-9]{6}[ -]?[0-9]{5})\b/i,
...
(function() {
    var tableName = 'incident';
    var fieldsToCheck = ['description', 'short_description', 'comments', 'work_notes']; 

    var piiPatterns = [
        // Social Security Numbers (SSNs) - handling spaces and hyphens
        /\b\d{3}[ -]?\d{2}[ -]?\d{4}\b/i, 

        // Credit Card Numbers - handling spaces and hyphens
        /\b(?:4[0-9]{3}[ -]?[0-9]{4}[ -]?[0-9]{4}[ -]?[0-9]{4}|5[1-5][0-9]{2}[ -]?[0-9]{4}[ -]?[0-9]{4}[ -]?[0-9]{4}|3[47][0-9]{2}[ -]?[0-9]{6}[ -]?[0-9]{5})\b/i,

        // Date of Birth (DOB) - various formats, including your specific examples
        /\b(0[1-9]|1[0-2])\/(0[1-9]|[12][0-9]|3[01])\/\d{4}\b/i,   // MM/DD/YYYY
        /\b(0[1-9]|[12][0-9]|3[01])\/(0[1-9]|1[0-2])\/\d{4}\b/i,   // DD/MM/YYYY
        /\b\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])\b/i,    // YYYY-MM-DD
        /\b\d{2}(0[1-9]|1[0-2])\d{4}\b/i,                           // DDMMYYYY
        /\b(0[1-9]|1[0-2])\d{2}\d{4}\b/i,                           // MMDDYYYY
        /\b\d{2}-\d{2}-\d{2}\b/i                                  // DD-MM-YY
    ];

    // Batch processing settings
    var batchSize = 1000; 

    // *************************************
    // Incident Table Processing 
    // *************************************
    var grIncident = new GlideRecord(tableName);
    grIncident.query();
    var totalCount = grIncident.getRowCount();
    var processedCount = 0;

    while (processedCount < totalCount) {
        grIncident.setLimit(batchSize);
        grIncident.query();

        while (grIncident.next()) {
            var incidentUpdated = false;

            fieldsToCheck.forEach(function(field) {
                if (grIncident[field]) {
                    var originalFieldValue = grIncident[field].toString();
                    var fieldValue = originalFieldValue;

                    piiPatterns.forEach(function(pattern) {
                        if (pattern.test(fieldValue)) {
                            fieldValue = fieldValue.replace(pattern, '[REDACTED]');
                            incidentUpdated = true;

                            // Log the change with incident number
                            gs.log('Redacted PII in incident ' + grIncident.number + '.' + field + 
                                   ': ' + originalFieldValue + ' -> ' + fieldValue); 
                        }
                    });

                    grIncident[field] = fieldValue;
                }
            });

            if (incidentUpdated) {
                grIncident.update();
            }
        }

        processedCount += batchSize;
        gs.log('Processed ' + processedCount + ' records for table: ' + tableName);
    }

    gs.log('PHI/PII removal script completed for table: ' + tableName);

    // *************************************
    //  sys_audit Table Processing 
    // *************************************

    // Now handle the activity stream (sys_audit) - include incident number in logs
    var grAudit = new GlideRecord('sys_audit');
    grAudit.addQuery('tablename', tableName); 
    grAudit.addQuery('fieldname', 'IN', fieldsToCheck.join(',')); 
    grAudit.query();
    totalCount = grAudit.getRowCount();
    processedCount = 0;

    while (processedCount < totalCount) {
        grAudit.setLimit(batchSize);
        grAudit.query();

        var transaction = new GlideTransaction();
        transaction.begin();

        while (grAudit.next()) {
            var auditUpdated = false;

            ['newvalue', 'oldvalue'].forEach(function(field) {
                if (grAudit[field]) {
                    var originalFieldValue = grAudit[field].toString();
                    var fieldValue = originalFieldValue;
                    piiPatterns.forEach(function(pattern) {
                        if (pattern.test(fieldValue)) {
                            fieldValue = fieldValue.replace(pattern, '[REDACTED]');
                            auditUpdated = true;

                            // Log the change with incident number
                            gs.log('Redacted PII in sys_audit for incident ' + grAudit.documentkey + '.' + field + 
                                   ': ' + originalFieldValue + ' -> ' + fieldValue);
                        }
                    });

                    // Use setValue() to update the field
                    var element = grAudit.getElement(field);
                    if (element && element.canWrite()) {
                        element.setValue(fieldValue);
                    } 
                }
            });

            if (auditUpdated) {
                try {
                    grAudit.update();
                } catch (error) {
                    gs.error('Error updating record in sys_audit: ' + error);
                    transaction.rollback();
                    return;
                }
            }
        }

        transaction.commit();

        processedCount += batchSize;
        gs.log('Processed ' + processedCount + ' records for table: sys_audit');
    }

    gs.log('PHI/PII removal script completed for table: sys_audit');
})();

 

0 Helpfuls
  • 367 Views
4 REPLIES 4

Gangadhar Ravi
Giga Sage
Giga Sage

‎08-30-2024 12:50 PM

Try this updated script.

 

(function() {
    var tableName = 'incident';
    var fieldsToCheck = ['description', 'short_description', 'comments', 'work_notes']; 

    var piiPatterns = [
        // Social Security Numbers (SSNs) - handling spaces and hyphens
        /\b\d{3}[ -]?\d{2}[ -]?\d{4}\b/i, 

        // Credit Card Numbers - handling spaces and hyphens
        /\b(?:4[0-9]{3}[ -]?[0-9]{4}[ -]?[0-9]{4}[ -]?[0-9]{4}|5[1-5][0-9]{2}[ -]?[0-9]{4}[ -]?[0-9]{4}[ -]?[0-9]{4}|3[47][0-9]{2}[ -]?[0-9]{6}[ -]?[0-9]{5})\b/i,

        // Date of Birth (DOB) - various formats
        /\b(0[1-9]|1[0-2])\/(0[1-9]|[12][0-9]|3[01])\/\d{4}\b/i,   // MM/DD/YYYY
        /\b(0[1-9]|[12][0-9]|3[01])\/(0[1-9]|1[0-2])\/\d{4}\b/i,   // DD/MM/YYYY
        /\b\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])\b/i,    // YYYY-MM-DD
        /\b\d{2}(0[1-9]|1[0-2])\d{4}\b/i,                         // DDMMYYYY
        /\b(0[1-9]|1[0-2])\d{2}\d{4}\b/i,                         // MMDDYYYY
        /\b\d{2}-\d{2}-\d{2}\b/i                                  // DD-MM-YY
    ];

    var batchSize = 1000;

    // *************************************
    // Incident Table Processing 
    // *************************************
    var grIncident = new GlideRecord(tableName);
    grIncident.query();
    var totalCount = grIncident.getRowCount();
    var processedCount = 0;

    while (processedCount < totalCount) {
        grIncident.setLimit(batchSize);
        grIncident.query();

        while (grIncident.next()) {
            var incidentUpdated = false;

            fieldsToCheck.forEach(function(field) {
                if (grIncident[field]) {
                    var originalFieldValue = grIncident[field].toString();
                    var fieldValue = originalFieldValue;

                    piiPatterns.forEach(function(pattern) {
                        if (pattern.test(fieldValue)) {
                            fieldValue = fieldValue.replace(pattern, '[REDACTED]');
                            incidentUpdated = true;

                            gs.log('Redacted PII in incident ' + grIncident.number + '.' + field + 
                                   ': ' + originalFieldValue + ' -> ' + fieldValue); 
                        }
                    });

                    grIncident[field] = fieldValue;
                }
            });

            if (incidentUpdated) {
                grIncident.update();
            }
        }

        processedCount += batchSize;
        gs.log('Processed ' + processedCount + ' records for table: ' + tableName);
    }

    gs.log('PHI/PII removal script completed for table: ' + tableName);

    // *************************************
    // sys_audit Table Processing 
    // *************************************

    var grAudit = new GlideRecord('sys_audit');
    grAudit.addQuery('tablename', tableName); 
    grAudit.addQuery('fieldname', 'IN', fieldsToCheck.join(',')); 
    grAudit.query();
    totalCount = grAudit.getRowCount();
    processedCount = 0;

    while (processedCount < totalCount) {
        grAudit.setLimit(batchSize);
        grAudit.query();

        while (grAudit.next()) {
            var auditUpdated = false;

            ['newvalue', 'oldvalue'].forEach(function(field) {
                if (grAudit[field]) {
                    var originalFieldValue = grAudit[field].toString();
                    var fieldValue = originalFieldValue;
                    piiPatterns.forEach(function(pattern) {
                        if (pattern.test(fieldValue)) {
                            fieldValue = fieldValue.replace(pattern, '[REDACTED]');
                            auditUpdated = true;

                            gs.log('Redacted PII in sys_audit for incident ' + grAudit.documentkey + '.' + field + 
                                   ': ' + originalFieldValue + ' -> ' + fieldValue);
                        }
                    });

                    // Use setValue() to update the field
                    var element = grAudit.getElement(field);
                    if (element && element.canWrite()) {
                        element.setValue(fieldValue);
                    } 
                }
            });

            if (auditUpdated) {
                try {
                    grAudit.update();
                } catch (error) {
                    gs.error('Error updating record in sys_audit: ' + error);
                    return;
                }
            }
        }

        processedCount += batchSize;
        gs.log('Processed ' + processedCount + ' records for table: sys_audit');
    }

    gs.log('PHI/PII removal script completed for table: sys_audit');
})();

 

 

0 Helpfuls

RBlor
Mega Guru
In response to Gangadhar Ravi

‎08-30-2024 01:11 PM - edited ‎08-30-2024 01:22 PM

so this ran successfully but my test inc with fake phi pii  still have the phi/pii in description /short description and comments and worknotes

RBlor_1-1725048738260.png

RBlor_3-1725049332587.png

 

RBlor_2-1725048750125.png

 

0 Helpfuls

Bert_c1
Kilo Patron
In response to RBlor

‎08-30-2024 05:11 PM - edited ‎08-30-2024 05:24 PM

It seems the check for 'incidentUpdated' in the script is not true when a value to be redacted is found. your use of defining a function in the 'forEach' may be the problem. good luck debugging your script.

0 Helpfuls

Gangadhar Ravi
Giga Sage
Giga Sage

‎08-30-2024 03:56 PM

You also need to delete sys_history_set record for that record in your secript so system will generate new one. 

 

Please mark my answer correct and helpful if this works for you.

0 Helpfuls