Data checks with Instance Scan: Task & Many-to-Many records with broken references

[Mark Roethof]

Hi there,

When talking about Instance Scan, mostly mentioned is the ability to perform checks on code, certain settings on Business Rules, Client Scripts, Script Includes, etcetera. In my opinion: Instance Scan can be utilized for way more! I've shared in the past several thoughts on this, like Sanity checks, Core Configuration checks, and... Data checks!

Especially the Data checks I would like to give some attention again. With this article, I will share two nice examples which will bring certain Data issues on production instances to the surface. Data checks that a System Administration should perform regularly, though which can be difficult to perform manually or which can be too time-consuming to perform. Data issues that might be minor, though which could also have serious consequences for related data / reporting / automation / etcetera.

Let's have a look at:
- Task records with broken references
- Many-to-many records with broken references

Task records with broken references

The first example I would like to share concerning data checks is a commonly seen issue on production instances: "Task records with broken references". For example task records where the "Assignment group" value or "Configuration item" value is invalid. Invalid as in it has a value, though the record doesn't exist. This can be just a minor issue, though it can also have huge consequences when there's logic based on that reference, when reporting, etcetera.

Especially on older instances, you could be faced with thousands of findings. Obviously you need to consider fixing the data, though also searching for the issue which is actually causing this. It might be an incorrect reference in a Flow, Workflow, Business Rule, scripting, integration, etcetera.

On larger instances, this check might run longer than the default maximum of 600 seconds and therefore end in a failure. Consider looking into this.

Script Only Check

(function (engine) {

    // Get all task extended tables
    var table_list = new TableUtils('task').getAllExtensions() + '';
    table_list = table_list.replace('[', '').replace(']', '');

    // Define arrays
    var reference_field = [],
        sysid_arr = [];

    // Query reference fields
    var grDictionaryEntry = new GlideRecord('sys_dictionary');
    grDictionaryEntry.addEncodedQuery('internal_type=reference^active=true^nameIN' + table_list);
    grDictionaryEntry._query();

    while(grDictionaryEntry._next()) {
        var getRecord = new GlideRecord(grDictionaryEntry.getValue('name'));
        getRecord.addEncodedQuery(grDictionaryEntry.getValue('element') + 'ISNOTEMPTY^' + grDictionaryEntry.getValue('element') + '.sys_idISEMPTY');
        getRecord.addActiveQuery();
        getRecord._query();

        // Create scan finding
        while(getRecord._next()) {
            if(sysid_arr.indexOf(getRecord.getUniqueValue()) == -1) {
                sysid_arr.push(getRecord.getUniqueValue());

                engine.finding.setCurrentSource(getRecord);
                engine.finding.increment();
            }
        }
    }

})(engine);

Many-to-many records with broken references

The second example I would like to share concerning data checks is concerning a situation that I came across at a few customers: "Many-to-many records with broken references". For example many-to-many records which have been created, manually or through scripted logic, where somehow the "from field" value or the "to field" value is invalid. Invalid as in it has a value, though the record doesn't exist. Obviously this could lead to the relationship between both records not working, not being visible, logic based on this which won't work, etcetera.

Obviously you need to consider fixing the data, though also searching for the issue which is actually causing this. It might be an incorrect reference in a Flow, Workflow, Business Rule, scripting, integration, etcetera.

Script Only Check

(function (engine) {

    // Query M2M Definition records
    var grM2MDefinition = new GlideRecord('sys_m2m');
    grM2MDefinition.addEncodedQuery('m2m_tableISNOTEMPTY^m2m_to_fieldISNOTEMPTY^m2m_from_fieldISNOTEMPTY');
    grM2MDefinition._query();

    while(grM2MDefinition._next()) {
        if(gs.tableExists(grM2MDefinition.getValue('m2m_table')) && gs.tableExists(grM2MDefinition.getValue('from_table')) && gs.tableExists(grM2MDefinition.getValue('to_table'))) {
            var getRecord = new GlideRecord(grM2MDefinition.getValue('m2m_table'));
            getRecord.addEncodedQuery(grM2MDefinition.getValue('m2m_to_field') + 'ISNOTEMPTY^' + grM2MDefinition.getValue('m2m_to_field') + '.sys_idISEMPTY^NQ' + grM2MDefinition.getValue('m2m_to_field') + 'ISNOTEMPTY^' + grM2MDefinition.getValue('m2m_to_field') + '.sys_idISEMPTY');
            getRecord.addActiveQuery();
            getRecord._query();

            // Create scan finding
            while(getRecord._next()) {
                engine.finding.setCurrentSource(getRecord);
                engine.finding.increment();
            }
        }
    }

})(engine);

---

And that's it! Two examples of data checks, which are hard to perform manually though which are quite important. Data checks which could be perfectly executed using Instance Scan.

I do have to review the code a bit, nested queries... maybe your code good practices Scan Checks will bring this up 🙂

👍	If this post helped you in any way, I would appreciate it if you hit bookmark or mark it as helpful. Interested in more articles, blogs, videos, and Share projects on Instance Scan I published? - Instance Scan

Kind regards,
Mark
2020-2022 ServiceNow Community MVP
2020-2022 ServiceNow Developer MVP

---

LinkedIn

[Maik Skoddow]

Hi @Mark Roethof 

does the code for the M2M broken reference check have an error?

Instead of querying the m2m_to_field two times:

getRecord.addEncodedQuery(
	grM2MDefinition.getValue('m2m_to_field') + 'ISNOTEMPTY^' + 
	grM2MDefinition.getValue('m2m_to_field') + '.sys_idISEMPTY^NQ' + 
	grM2MDefinition.getValue('m2m_to_field') + 'ISNOTEMPTY^' + 
	grM2MDefinition.getValue('m2m_to_field') + '.sys_idISEMPTY'
);

it should be:

getRecord.addEncodedQuery(
	grM2MDefinition.getValue('m2m_to_field') + 'ISNOTEMPTY^' + 
	grM2MDefinition.getValue('m2m_to_field') + '.sys_idISEMPTY^NQ' + 
	grM2MDefinition.getValue('m2m_from_field') + 'ISNOTEMPTY^' + 
	grM2MDefinition.getValue('m2m_from_field') + '.sys_idISEMPTY'
);