Learning to write Fix Scripts in ServiceNow – Part 2 – Queries

In this post, we will continue where we left off in Part 1, creating a Fix Script in ServiceNow. In Part 1, we wrote a basic script that will output the name of all of the records in the ‘cmdb_ci_computer’ table.

In this post, we will discuss how we can filter that list of returned records down to something more specific.

Let’s start in out console and find something simple to filter on.

cmdb_ci_computer table

If we take a look at the contents of our computer table, (cmdb_ci_computers.list) we will find we have plenty of data to filter on. So let’s just choose Manufacturer. And let’s just pick Lenovo (These were selected randomly). Seems simple enough. If (‘Manufacturer’ equals ‘Lenovo’) { Do Something }. So let’s take a look at the same ServiceNow doc we looked at in Part 1 that goes over querying tables.

Line 2 – addQuery

We can see in the example provide, they want to query the ‘incident’ table in line 1. In line 2 we see the details of the query the want to provide: the column name and the value. In this case, it is all records with a priority of ‘1’. We may also notice that there is not an operator expressed here. When no operator is defined, it assumes ‘Equals’.
Example: target.addQuery('priority',1);

This should be easy enough to translate into our block of code:

var recName = new GlideRecord('cmdb_ci_computer');
recName.query();
while (recName.next()) {
gs.print(recName.name);
}

We just want to include the addQuery method like the example shows: recName.addQuery('Manufacturer','Lenovo');

Which should end up like this:

var recName = new GlideRecord('cmdb_ci_computer');
recName.addQuery('Manufacturer','Lenovo');
recName.query();
while (recName.next()) {
gs.print(recName.name);
}

But when we run this script, it returns an error! Why?!

QueryEventLogger: Invalid query detected, please check logs for details [Unknown field Manufacturer in table cmdb_ci_computer]
Invalid query detected, stack trace below [Unknown field Manufacturer in table cmdb_ci_computer]
com.glide.db.QueryEventLogger.logInvalidQuery(QueryEventLogger.java:56)
com.glide.db.QueryEventLogger.logInvalidQuery(QueryEventLogger.java:47)
com.glide.script.GlideRecord.isInvalidTableField(GlideRecord.java:2372)
com.glide.script.GlideRecord.jsFunction_addQuery(GlideRecord.java:2023)
sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
org.mozilla.javascript.MemberBox.invoke(MemberBox.java:138)
org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:670)
org.mozilla.javascript.FunctionObject.call(FunctionObject.java:614)
org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2582)
org.mozilla.javascript.optimizer.OptRuntime.call2(OptRuntime.java:42)
org.mozilla.javascript.gen._refname__32334._c_script_0(<refname>:3)
org.mozilla.javascript.gen._refname__32334.call(<refname>)
org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:563)
org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3429)
org.mozilla.javascript.gen._refname__32334.call(<refname>)
org.mozilla.javascript.gen._refname__32334.exec(<refname>)
com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:279)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:118)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:82)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:73)
com.glide.script.Evaluator.evaluateString(Evaluator.java:96)
com.glide.update.UpdateManager2.testFixScript(UpdateManager2.java:687)
com.snc.apps.ScriptFixXMLHttpProcessor$ScriptFixWorker.runFixScript(ScriptFixXMLHttpProcessor.java:173)
com.snc.apps.ScriptFixXMLHttpProcessor$ScriptFixWorker.evaluateScript(ScriptFixXMLHttpProcessor.java:151)
com.snc.apps.ScriptFixXMLHttpProcessor$ScriptFixWorker.startWork(ScriptFixXMLHttpProcessor.java:126)
com.glide.worker.AbstractProgressWorker.startAndWait(AbstractProgressWorker.java:126)
com.glide.worker.ProgressWorker.startAndWait(ProgressWorker.java:52)
com.glide.worker.AbstractProgressWorker.start(AbstractProgressWorker.java:101)
com.snc.apps.ScriptFixXMLHttpProcessor.process(ScriptFixXMLHttpProcessor.java:87)
com.glide.processors.XMLHttpProcessor.processJavaAJAX(XMLHttpProcessor.java:169)
com.glide.processors.XMLHttpProcessor.process(XMLHttpProcessor.java:121)
com.glide.processors.AProcessor.runProcessor(AProcessor.java:553)
com.glide.processors.AProcessor.processTransaction(AProcessor.java:241)
com.glide.processors.ProcessorRegistry.process0(ProcessorRegistry.java:177)
com.glide.processors.ProcessorRegistry.process(ProcessorRegistry.java:166)
com.glide.ui.GlideServletTransaction.process(GlideServletTransaction.java:31)
com.glide.sys.Transaction.run(Transaction.java:2218)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
java.lang.Thread.run(Thread.java:748)

OK. Line 1 in our error states ‘unknown field Manufacturer in table cmdb_ci_computer’. I know this field exists, I am looking at it in my console.

After a bit of trial and error, I replace the value ‘Manufacturer’ with ‘manufacturer’. I changed the upper case M to a lower case m. Now when I run the query, I do NOT get an error. Oddly enough, I do not get any results either.

0 results

Again, I know I have records where the Manufacturer is Lenovo. I can manually apply the filter just fine in my console. So why am I not getting any results from my script?

Then I remembered a tip I was given one time. We can right-click on a query link in the browser window, and one of the options provided by ServiceNow is “Copy query”. So I did this. I right clicked my query ‘All > Manufacturer = Lenovo’ in my browser window, and selected ‘Copy query’.

These are the results I got: manufacturer=aa0a6df8c611227601cd2ed45989e0ac

I have to say, I did not expect that at all. It looks to me like ServiceNow is using the sys_id of the Manufacturer value. (We will also notice the ‘manufacturer’ value in all lower-case.)

Let’s replace the line in our script with these values.

var recName = new GlideRecord('cmdb_ci_computer');
recName.addQuery('manufacturer','aa0a6df8c611227601cd2ed45989e0ac');
recName.query();
while (recName.next()) {
gs.print(recName.name);
}

Now that line 2 contains new values, let’s see what happens when we run this.

Results – Lenovo Computers

Finally! Our fix script works, and it returns only the computers with the Manufacturer Lenovo.

In our next post, we are going to cover some more queries and the options we have available to us when things need to get more complex.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s