Scheduled job not picking up relevant records based on one small change to the filter.

[snow_beginner]

Hi, I was recently assigned to change the fitler criteria of a scheduled job. The filter was basically to look into table 1 and pick all the records which start with lbg- and put them into another table if they had been updated in the last 2 days, the requirement was to change that to pick records which contains lbg. So literally just an operator change. When I first did the change in dev it worked, but when the change moved to test it stopped working, now its not working in dev either.

Here is the code below as it was when I didn't change it:

//Review all records updated from last week from Network Nodes table
var grXBBCNN = new GlideRecord('x_btesi_bt_cmdb_network_nodes');

grXBBCNN.addEncodedQuery("u_ntn_managed_flag=Y^sys_updated_onRELATIVEGT@dayofweek@ago@2");
//grXBBCNN.addEncodedQuery("ntn_man_host_nameSTARTSWITHLBG-");

//grXBBCNN.setLimit(22);
grXBBCNN.query();
while (grXBBCNN.next()) {
	
    //Initialize BT Network Node table
    var grUCCBNN = new GlideRecord('u_cmdb_ci_bt_network_node');

    //Get BT Product Type and Model Number from Port Node Product table
    var grXBBCPNPT = new GlideRecord('x_btesi_bt_cmdb_port_node_products');
    grXBBCPNPT.get(grXBBCNN.getValue('ntn_ndp_id'));
    var btProductType = grXBBCPNPT.getElement('ndp_ndt_id.ndt_name').getValue() || '';
	var modelNumber = grXBBCPNPT.getValue('ndp_name') || '';

... rest of script which picks the fields and does things
	
	

I literally only changed the line 

grXBBCNN.addEncodedQuery("ntn_man_host_nameSTARTSWITHLBG-"); to

grXBBCNN.addEncodedQuery("ntn_man_host_nameCONTAINSLBG"); and didn't touch anything else in the rest of the code. Like I said it worked once in dev, but didn't work in test and now when I look at it in dev, its again not working there either. 

Here is what I have tried so far:

I built the actual filter in the table and saw that when you copy query, you get LIKE not contains so I changed it to grXBBCNN.addEncodedQuery("ntn_man_host_nameLIKELBG"); - it didn't work.

I also tried to have just one encoded query like 

('u_ntn_managed_flag=Y^ntn_man_host_nameLIKELBG^ORntn_man_host_nameLIKElbg^sys_updated_onRELATIVEGT@dayofweek@ago@2'); - doesn't work

I have tried to split each query like:

var twoDaysAgo = new GlideDateTime();

twoDaysAgo.addDaysUTC(-2);

grXBBCNN.Query('u_ntn_managed_flag', 'Y');

grXBBCNN.Query('sys_updated_on','>=', twoDaysAgo); // tried to do this <= as well and didnt work

grXBBCNN.addQuery('ntn_man_host_name','CONTAINS','LBG');

What's more interesting is, if I take the script from another instance where it hasn't been changed and paste it into background script, I don't get an error. But if I paste the changed script (first change of changing startswith to contains) I get an error

Script execution error: Script Identifier: null.null.script, Error Description: java.lang.NullPointerException: Cannot invoke "Object.toString()" because "value" is null, Script ES Level: 0
Couldn't decipher the stack trace resulting from the following JavaScriptException:
java.lang.NullPointerException: Cannot invoke "Object.toString()" because "value" is null: org.mozilla.javascript.JavaScriptException: java.lang.NullPointerException: Cannot invoke "Object.toString()" because "value" is null: 	org.mozilla.javascript.Context.makeJavaScriptException(Context.java:2631)
	org.mozilla.javascript.Context.throwAsScriptRuntimeEx(Context.java:2615)
	org.mozilla.javascript.MemberBox.invoke(MemberBox.java:243)

Can someone please tell me what's going on and why its not working at all anymore.

[RaghavSh]

When you say not picking relevant records what does that mean?

Also ntn_man_host_name should be a string type field.

Please mark the answer correct/helpful accordingly.

[snow_beginner]

Hi @RaghavSh 

What I mean when I say its not picking up records is this:

There is a schduled job which runs every morning at 2 am. It looks into this table x_btesi_bt_cmdb_network_nodes and see if there are any records which have been updated in the last 2 days and puts them into this table u_cmdb_ci_bt_network_node.

The filter for this scheduled job is that it will pick up records where the name contains lbg, updated in last 2 days and managed flag is Y. It will not pick up records where it doesn't meet this criteria. So if for example managed flag=n or updated 5 days ago or the name doesn't contain lbg those records wont be picked up.

I am no longer using CONTAINS. In all the queries I am using LIKE now. but when I execute the job, the test records I made in the table x_btesi_bt_cmdb_network_nodes are not being pushed to u_cmdb_ci_bt_network_node even though they are created/updated today, managed flag is Y and name contains lbg. 

It was working fine when the name was starts with lbg, but since I changed the filter to contains (LIKE) its not picking up any records.

Yes the name field is string.

[Ankur Bawiskar]

@snow_beginner 

then try debugging 1 by 1

Since you mentioned you did only small change then some issue within the script only

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[M Iftikhar]

Hi @snow_beginner ,

The issue is with the encoded query syntax — CONTAINS isn’t a valid operator in ServiceNow encoded queries.

You should use LIKE instead of CONTAINS. Here’s the corrected version I tested and confirmed works fine:

(function execute() {
    // STARTSWITH test
    var gr = new GlideRecord('u_x_test_network_nodes');
    gr.addEncodedQuery("u_ntn_managed_flag=Y");
    gr.query();

    gs.info("==== Records with STARTSWITH LBG- ====");
    while (gr.next()) {
        if (gr.u_ntn_man_host_name && gr.u_ntn_man_host_name.startsWith("LBG-")) {
            gs.info("Matched record (STARTSWITH): " + gr.u_ntn_man_host_name);
        }
    }

    // CONTAINS test using LIKE
    gs.info("==== Records with CONTAINS LBG ====");
    var gr2 = new GlideRecord('u_x_test_network_nodes');
    gr2.addEncodedQuery("u_ntn_managed_flag=Y^u_ntn_man_host_nameLIKELBG");
    gr2.query();

    while (gr2.next()) {
        gs.info("Matched record (CONTAINS): " + gr2.u_ntn_man_host_name);
    }
})();

Output:

==== Records with STARTSWITH LBG- ====
Matched record (STARTSWITH): LBG-123
==== Records with CONTAINS LBG ====
Matched record (CONTAINS): abcLBG567
Matched record (CONTAINS): testLBG567
Matched record (CONTAINS): abc-LBG
Matched record (CONTAINS): LBG-123

So instead of:

ntn_man_host_nameCONTAINSLBG

use:

ntn_man_host_nameLIKELBG

That’s why your previous version threw a NullPointerException — CONTAINS isn’t valid syntax for encoded queries.

Or you can also use this safer version:

(function execute() {
    gs.info("==== Records with CONTAINS LBG ====");
    var gr = new GlideRecord('u_x_test_network_nodes');
    gr.addQuery('u_ntn_managed_flag', 'Y');
    gr.addNotNullQuery('u_ntn_man_host_name');
    gr.addQuery('u_ntn_man_host_name', 'LIKE', '%LBG%');
    gr.query();
    while (gr.next()) {
        gs.info("Matched (CONTAINS): " + gr.u_ntn_man_host_name);
    }
})();

I tested both on my custom table (u_x_test_network_nodes) — they work fine.

If my response helped, please mark it as the accepted solution so others can benefit as well. 

[snow_beginner]

I will test it out and see what happens, but so far changing CONTAINS to LIKE has not been working, but I will split the query up and use LIKE and see if that works. Thanks!