Background script | GlideRecord Query not working properly for sys_history_line table

[Pbo1990]

Hi everyone,

I stumbled upon a really weird issue I can't wrap my head around.

I am trying to populate a date field in a table based on historical data of specific field-updates ("Status" field updates in the sys_history_line table of the records).

Since I am dealing with ~1600 records, I wanted to accomplish this via a background script - you can find the code below and in the attached screenshot.

The script gets all records of a custom extension of the task table, that are in any closed state and tries to query for those records sys_ids in the history table with the "state" field requirement.

When testing this on single records with their individual sys_ids, I don't have any issues, but when applying it to all 1600 records, I get for most records no match - even though each record that is in a closed state, has to have a historic event, where the state was actually set to any of those closed states. 

However, and this is the weird part, once I open a record in the environment manually (List of records of the queried table, and simply opening any of the records), this record will then be queried correctly with the script, but not before that.

My question:

> Is there any necessity to initialize records in an environment before the referencing with the sys_history_line tables work? When testing myself, I can also not use the "Set" column of the sys_history_line table for filtering manually, before I have not opened the record, I am looking for, at least once.

Background:

> The DEV environment I am working in was just mirrored a few days ago from the production environment

I welcome any feedback that might help me to understand the underlying issue here and how to resolve it without opening each individual record before running the script.

Many thanks in advance!

Script below

_________________________________________________________

// query for closed states in table

var queryString = 'state=3^ORstate=4';

var gr = new GlideRecord('x_mscdx_ugc_om_ugc');

gr.addEncodedQuery(queryString);

gr.setLimit(20); //set limiter for testing

gr.query();

while (gr.next()){

     // create a GlideRecord of the sys_history_line table

    var history_gr = new GlideRecord('sys_history_line');

    // query for history entries with the respective sys_id of the record and the state field

    history_gr.addQuery('set.id',gr.sys_id);

    history_gr.addQuery('field','state')

    // put the matching records in order (e.g. all state changes) with latest at the top

    history_gr.orderByDesc('update_time');

    // get just the record on top (latest record)

    history_gr.setLimit(1);

    history_gr.query();

    //provide individual record information for testing

    gs.info(history_gr.getRowCount());

    gs.info(gr.number);

    gs.info(gr.sys_id);

    if (history_gr.next()) {

        // set the archiving date of the current normal record to the update-time of the status field + x days

        // variable for storing glide records

        var archiving_gdt = new GlideDateTime(history_gr.update_time);

        archiving_gdt.addDaysUTC(730);

        gs.info("update_time:" + archiving_gdt);

    }

}

_________________________________________________________

[Pbo1990]

I already might have found the reason why this behavior is observed - the sys_history_line tables are rotated out every 28 days and can't be properly queried anymore after this timeframe.

I would be grateful if someone knows a better way of getting historical field update values out of ServiceNow 🙂 ... 

[Dan Ostler]

Another option would be to use data in the sys_audit table. However, I recommend checking out the HistoryWalker API for doing this. It's a great abstraction layer for handling this sort of thing, and the provide a great way of switching between using data in the sys_audit, and sys_history_line tables. Take note of the constructor overloading that is used in this class, the second constructor listed accepts a third argument that allows you to specify if you want to use Audit or History Set data.

[Pbo1990]

Hi Dan,

thanks again for the proposal and the provided API!

That actually looks like a good way to parse through the different updates of the records. However, I have used this method for trying to find a change within a status field for records and it did not work the way I expected it to.

My assumption is, that when using the hw.walkBackward() / hw.walkForward() functions, you walk through all updates of a record (beginning differs).

However, as you can see in the sample provided (screenshot), the output jumps from update 17 to update 0 and, hence, the state-change can't be observed. (always 3, walkBackward was used)

Have you experienced a behavior like this before?  

Based on my understanding, the sys_audit table should hold all the updates, not just a subset.

Any feedback / input is again welcome!

[Dan Ostler]

Fortunately, this should be a really easy fix. All you should have to do is modify this one line of code:

// From this:
var hw = new sn_hw.HistoryWalker(gr.getTableName(), gr.getUniqueValue());

// To this:
var hw = new sn_hw.HistoryWalker(gr.getTableName(), gr.getUniqueValue(), true);

HistoryWalker uses History Set data by default (which in most cases is totally fine, and more efficient). By passing in this third argument you're specifying that you want to use data in the Audit table instead. I hope this helps!