Join the #BuildWithBuildAgent Challenge! Get recognized, earn exclusive swag, and inspire the ServiceNow Community with what you can build using Build Agent.  Join the Challenge.

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
  • 497 Views
4 REPLIES 4

Gangadhar Ravi
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

‎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