Community Code Snippets: A GlideRecord Survey - Part 2 (Intermediate)

sabell2012 · ‎08-15-2018

NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.

DIFFICULTY LEVEL: INTERMEDIATE
Assumes having taken the class SSNF and has good intermediate level of knowledge and/or familiarity with Scripting in ServiceNow.

In my first article (Community Code Snippets: A GlideRecord Survey - Part 1 (Beginner)) I covered the basics of GlideRecords by presenting a series of progressive examples on how to assemble simple queries. I also presented, where applicable, the SQL equivalents to those queries. In this, Part 2, article I will be continuing where I left off, and presenting the following:

new
BETWEEN
IN
NOT IN
CONTAINS
DOES NOT CONTAIN
LIKE
NOT LIKE
STARTSWITH
ENDSWITH
Combining Statements
Alternatives to .addNotNullQuery
INSTANCEOF
Stuff that didn't work
Try/Catch Revisited

Okay so let's get started!

Axioms

All string match queries are case agnostic. This is actually a SQL thing.
All operators are string sensitive! For example 'STARTSWITH' cannot be represented in lowercase 'startswith'; it will not work!

Caveats

I'm using the London Patch 0 release. This should not make a difference though. Any version after Helsinki (or maybe earlier) may work. Probably.
All values I use in the following queries are ones I had on my personal developer instance. Your personal developer instances may not have the same data present. If so...improvise!
I will be doing all of these examples in Scripts - Background, but they will work fine in a Fix Script.

Useful Links

Using GlideRecord To Query Tables

Operators available for filters and queries

MySQL :: SQL Statement Syntax

The "new" Command

First I want to bring something up that developers WILL run into when working with GlideRecords: Forgetting to "new" the GlideRecord object!

If you leave off the "new" what happens? Nothing. It operates normally. In earlier versions this flat out didn't work. Something "new" has been added! (pun intended). So what does the "new" do? It instantiates (fancy programming term for "brings into existence") the object using GlideRecord as the template. "New" forces the initialize function to fire, and the parameter, in this case the table name "incident", to be fed into the initialize. Usually the "new" is required. Oddly enough, it does not seem to really be needed; making GlideRecord more like a function than a class object. This might have something to do with the fact that GlideRecord is really a Java object (and rigged in JavaScript to be self-newing). Use the "new". It is a best practice!

Try it out! BUT DON'T DO THIS! It is not "safe" in older versions of the platform. 😊

var incidents = GlideRecord('incident');
incidents.setLimit(10);
incidents.orderByDesc('number');
incidents.query();

while (incidents.next()) {
    gs.info('---> number: ' + incidents.getValue('number'));
}

The "BETWEEN" Operator

Ok, so remember in my last article about BETWEEN not being available? Well, far be it for me to not admit when I am wrong! Um, I was wrong. It was the nomenclature that I was NOT aware of! So while looking at operators for encoded queries (for my next article in this series); what should I run across? You got it! The BETWEEN WITH AN EXAMPLE! WOW! An example! This was for an encoded query, but maybe it would work in a regular GlideRecord query!? It did! Check it out:

var incidents = new GlideRecord('incident');
// well this was intuitively obvious...not!
incidents.addQuery('number', 'BETWEEN', 'INC0010060@INC0010070'); 
incidents.query();

while (incidents.next()) {
    gs.info('---> number: ' + incidents.getValue('number'));
}

MYSQL SQL transliteration:

SELECT * FROM incident WHERE number BETWEEN 'INC0010060' AND 'INC0010070';

Nice! This whole time it was hiding in plain sight!

The "IN" Operator

Now let's talk about operators. My favorite of the bunch, yes...yes...I have a favorite, is the 'IN' operator. This little gem is like an addOrCondition on steroids. It takes an array list as a comparator value and generates the "OR"'s under-the-hood.

For example:

var numbersList = ['INC0010070','INC0010065','INC0010157'];

var incidents = new GlideRecord('incident');
incidents.addQuery('number', 'IN', numbersList);
incidents.query();

while (incidents.next()) {
    gs.info('---> number: ' + incidents.getValue('number'));
}

This will return the records listed in the array (if they exist). The SQL would look like this:

SELECT * FROM incident WHERE number IN ('INC0010070','INC0010065','INC0010157');

What is cool about this is that you can assemble lists of things (sys_ids for example) in an array and then feed it to your GlideRecord. This allows you to dynamically build a one-dimensional array of values and smart-constrain your query to only return records you are interested in. This is one of my favorite tools when working with massive numbers of records (like with CMDB), because you can dynamically build a one-dimensional array then feed it to the GlideRecord to retrieve your tailored result. Cool, huh?! One of the better kept secrets for the longest time.

The "NOT IN" Operator

So the reverse is simple to use. The NOT IN returns everything, but the values in the list. However, careful with this one, it could return a zillion records depending on what table you are going against! 🙂

var numbersList = ['INC0010070','INC0010065','INC0010157'];

var incidents = new GlideRecord('incident');
incidents.addQuery('number', 'NOT IN', numbersList);
incidents.query();

// returns a skillion records...ok, maybe not that many, but it is a lot!
while (incidents.next()) {
    gs.info('---> number: ' + incidents.getValue('number'));
}

This will return the records listed in the array (if they exist). The SQL would look like this:

SELECT * FROM incident WHERE number NOT IN ('INC0010070','INC0010065','INC0010157');

CONTAINS

Operates the same as the .indexOf in JavaScript. It searches the target field for the specified string, and if present returns those records where found. This is the same as a LIKE '%ab%' which I will describe later. There does not seem to be any performance hit I can find with either technique.

var users = new GlideRecord('sys_user');
users.addQuery('name', 'CONTAINS', 'ab');
users.query();

while (users.next()) {
    gs.info('---> Name: ' + users.getValue('name'));
}

SQL equivalent:

SELECT * FROM sys_user WHERE name LIKE '%ab%';

DOES NOT CONTAIN

This is the opposite of CONTAINS. Period. Warning! This can return a bunch (programmer technical term) of records!

var users = new GlideRecord('sys_user');
users.addQuery('name', 'DOES NOT CONTAIN', 'ab');
users.query();

while (users.next()) {
    gs.info('---> Name: ' + users.getValue('name'));
}

SQL equivalent:

SELECT * FROM sys_user WHERE name NOT LIKE '%ab%';

LIKE

So here is the analog for CONTAINS. Pick your favorite of the two and stick with it. 🙂

var users = new GlideRecord('sys_user');
users.addQuery('name', 'LIKE', '%ab%');
users.query();

while (users.next()) {
    gs.info('---> Name: ' + users.getValue('name'));
}

SQL equivalent:

SELECT * FROM sys_user WHERE name LIKE '%ab%';

NOT LIKE

And, of course, the analog for DOES NOT CONTAIN. Fewer keystrokes for you lazy programmers out there! 😊

var users = new GlideRecord('sys_user');
users.addQuery('name', 'NOT LIKE', '%ab%');
users.query();

while (users.next()) {
    gs.info('---> Name: ' + users.getValue('name'));
}

SQL equivalent:

SELECT * FROM sys_user WHERE name NOT LIKE '%ab%';

STARTSWITH

This is another one I sometimes find useful. Tools in the tool box! Grabs all records that start with the given character(s).

var users = new GlideRecord('sys_user');
users.addQuery('name', 'STARTSWITH', 'a');
users.query();

while (users.next()) {
    gs.info('---> Name: ' + users.getValue('name'));
}

SQL equivalent:

SELECT * FROM sys_user WHERE name LIKE 'a%';

ENDSWITH

Grabs all records that end with the given character(s).

var users = new GlideRecord('sys_user');
users.addQuery('name', 'ENDSWITH', 'er');
users.query();

while (users.next()) {
    gs.info('---> Name: ' + users.getValue('name'));
}

SQL equivalent:

SELECT * FROM sys_user WHERE name LIKE '%er';

Combining Statements

And, of course, you can combine statements. Remember, these are all "AND"ed. Try a few! Knock yourself out! 😁

var users = new GlideRecord('sys_user');
users.addQuery('name', 'STARTSWITH', 'a');
users.addQuery('name', 'ENDSWITH', 'er');
users.addQuery('name', 'DOES NOT CONTAIN', 'ab');
users.query();

while (users.next()) {
    gs.info('---> Name: ' + users.getValue('name'));
}

SQL equivalent:

SELECT * FROM sys_user WHERE name LIKE 'a%' AND name LIKE 'er%' AND name NOT LIKE '%ab%'

Alternative to .addNotNullQuery

The MySQL != works fine, and so does <>. I use != mostly, and never use <>.

var users = new GlideRecord('sys_user');
users.addQuery('name', 'STARTSWITH', 'ab');
users.addQuery('manager', '<>', null);
users.query();

while (users.next()) {
    gs.info('---> Name: ' + users.getValue('name') + ', manager: ' + users.manager.getDisplayValue());
}

SQL equivalent:

SELECT * FROM sys_user WHERE name LIKE 'ab%' AND manager <> null;

INSTANCEOF

This can be used to retrieve records from CMDB of a specified class AND all extended classes. Watch it! It can return a significant number of records! This is useful to, but not limited to CMDB extended tables. This has its uses and is definitely a great short-hand for the IN equivalent!

var cmdbRecords = new GlideRecord('cmdb');
cmdbRecords.addQuery('sys_class_name', 'INSTANCEOF', 'cmdb_ci_computer');
cmdbRecords.query();

while (cmdbRecords.next()) {
    gs.info('---> Class: ' + cmdbRecords.getValue('sys_class_name') + ', Name: ' + cmdbRecords.getValue('name'));
}

SQL equivalent:

SELECT * FROM cmdb WHERE sys_class_name IN ('cmdb_ci_computer', 'cmdb_ci_server', 'cmdb_ci_win_server', ...etc.!);

Other Commands Not Mentioned

For further commands and info about them check out the following link:

Operators available for filters and queries

List of Stuff I Tried, But Didn't Work

NOTBEWEEN
NOT BETWEEN
DOES NOT STARTWITH
IS
ISEMPTY
ISNOTEMPTY
IS NULL
IS NOT NULL
REGEXP (boy! I wished this worked!!!! There are scripting alternatives though)
<=>
EXIST
NOT EXISTS

Try/Catch Revisited

Remember where I said that Try/Catch will never work because GlideRecord eats it's errors? Okay, well, that isn't exactly the case. I kinda forgot a situation where it can/will trigger the try/catch. Sorry. 🙂

Uninitialized Variable Trigger

Since the variable garbage contains no value, but is, in fact, null. Boom. Try it!

try {
    var users = new GlideRecord('sys_user');
    users.addQuery(garbage, 'STARTSWITH', 'ab'); // null variable
    users.addQuery('manager', '<>', null);
    users.query();

    while (users.next()) {
        gs.info('---> Name: ' + users.getValue('name') + ', manager: ' + users.manager.getDisplayValue());
    }
}
catch(err) {
    // boom? yep!
    gs.info('---> ERROR: {0}', [err]);
}

So, cool stuff!

In my third article, of this series, I will be covering the handling of the results of a query. What you have available to you in the scripting side; what works, and does not work. More intermediate level stuff!

Enjoy!

Steven Bell.

If you find this article helps you, don't forget to log in and mark it as "Helpful"!

Originally published on: 08-15-2018 08:56 AM

I updated the code and brought the article into alignment with my new formatting standard.