Fix script to populate record on metric

[tsoct]

Hello all

We created a metric to track journal field updates. Each comments/worknotes should generate a metric instance.

In addition to this new metric, the requirement including to run a fix script on previous journal entries. I intend to execute the following fix script on non-working hours and break the filter down into months. Journal entries per month total roughly 90,000 records. May I ask if the script below is effective enough to process the 90K records?

Thank you

var queryString = "sys_created_onONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()";

var task = new GlideRecord('sys_journal_field');
task.addEncodedQuery(queryString);
task.query();

while (task.next()) {
    var user= task.sys_created_by;
    var usr = new GlideRecord('sys_user');
    usr.get('user_name', user);
    usr.query();

    if (usr.department != '' && usr.active == true) {
        var instanceRecord = new GlideRecord('metric_instance');
        var metricSysID = '78f6ee3687d89e50bcefdd383cbb35fe'; //sys id of new metric
        instanceRecord.initialize();
        instanceRecord.definition = metricSysID;
        instanceRecord.start = task.sys_created_on;
        instanceRecord.end = task.sys_created_on;
        instanceRecord.id = task.element_id;
        instanceRecord.field = task.element;
        instanceRecord.value = usr.name;
        instanceRecord.calculation_complete = true;
		instanceRecord.field_value = task.name;
        instanceRecord.insert();
    }

}

[Rajesh Chopade1 · Accepted solution]

Hi @tsoct 

Looks your script is functional but here having some suggestions for code optimization:

- Instead of calling .query() on the usr GlideRecord after using .get(), check if the record exists directly after .get(). The .query() call is unnecessary here and could add significant overhead.

- If you're processing this in batches of 90,000 records per month, make sure your script is run asynchronously, such as through a Scheduled Job with chunking to avoid timeouts. ServiceNow has an "out of box" capability to manage batch processing (e.g., using gs.eventQueue or triggering this script via a Scheduled Script Execution).

-If possible, avoid hard-coding the metricSysID. It’s better to dynamically look it up if it's likely to change. (you can use system property)

Have a looks once bellow optimized script:

var queryString = "sys_created_onONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()";

var task = new GlideRecord('sys_journal_field');
task.addEncodedQuery(queryString);
task.query();

while (task.next()) {
    var user = task.sys_created_by;
    var usr = new GlideRecord('sys_user');

    // Use get instead of query for efficiency
    if (usr.get('user_name', user) && usr.active && usr.department) {
        var instanceRecord = new GlideRecord('metric_instance');
        var metricSysID = '78f6ee3687d89e50bcefdd383cbb35fe'; //sys id of new metric

        instanceRecord.initialize();
        instanceRecord.definition = metricSysID;
        instanceRecord.start = task.sys_created_on;
        instanceRecord.end = task.sys_created_on;
        instanceRecord.id = task.element_id;
        instanceRecord.field = task.element;
        instanceRecord.value = usr.name;
        instanceRecord.calculation_complete = true;
        instanceRecord.field_value = task.name;
        instanceRecord.insert();
    }
    // Optional: Add a slight delay to avoid high load
    // gs.sleep(500);
}

I hope my answer helps you.

thank you

rajesh