In this comprehensive guide, we will take you through the fundamentals of GlideQuery, from basic concepts and syntax to advanced techniques and best practices. By the end of this article, you will have a solid grasp of how to leverage GlideQuery to enhance your data handling capabilities in ServiceNow, making your development process more efficient and your applications more responsive.

Let’s dive in and explore the power of GlideQuery, unlocking new potentials in your ServiceNow development journey.

Principles of GlideQuery

Peter Bell, a software engineer at ServiceNow, initially developed these API to use as an external tool for his team. Gradually, the API became an integral part of the platform, specifically integrated into the Paris release.

This API is versatile, compatible with both global and scoped applications. In the latter case, developers need to prefix their API calls with “global” to ensure proper functionality:

var user = new GlideQuery('sys_user')

var user = new global.GlideQuery('sys_user')

The API is entirely written in JavaScript and operates using GlideRecord in a second layer. Instead of replacing GlideRecord, its purpose is to enhance the development experience by minimizing errors and simplifying usage for developers.

I. Fail Fast

Detect errors as quickly as possible, before they become bugs.

Through a new type of error, NiceError, which facilitates the diagnosis of query errors, it is possible to know exactly what is wrong in your code and quickly fix it. Examples:

Field name validation

The API checks if the field names used in the query are valid and returns an error if any of them are not. In some cases, this is extremely important as it can prevent major issues. In the example below, using GlideRecord it would delete ALL records from the user table because the field name is written incorrectly. Note that the line that would delete the records has been commented out for safety and we have added a while loop just to display the number of records that would be deleted.

var gr = new GlideRecord('sys_user');
gr.addQuery('actived', '!=', true);
gr.query();
//gr.deleteMultiple();  commented for safety 
gs.info(gr.getRowCount());
while (gr.next()) {
    gs.info(gr.getDisplayValue('name'));
}

Using GlideQuery the API returns an error and nothing is executed:

var myGQ = new GlideQuery('sys_user')
    .where('activated', '!=', true)
    .del();

Returns:

NiceError: [2024-07-22T12:58:23.681Z]: Unknown field 'activated' in table 'sys_user'. Known fields:
[
  "country",
  "calendar_integration",
  etc.

Choice field validation

The API checks whether the option chosen for the field exists in the list of available options when it is of type choice. In the example below, using GlideRecord, the script would return nothing:

var gr = new GlideRecord('incident');
gr.addQuery('approval', 'donotexist'); //invalid value for the choice field 'approval'
gr.query();
while (gr.next()) {
    gs.info(gr.getDisplayValue());
}

Using GlideQuery, the API returns an error and nothing is executed:

var tasks = new GlideQuery('incident')
    .where('approval', 'donotexist')
    .select('number')
    .forEach(gs.log);

Returns:

NiceError: [2024-07-22T12:57:33.975Z]: Invalid choice 'donotexist' for field 'approval' (table 'incident'). Allowed values:
[
  "not requested",
  "requested",
  "approved",
  "rejected"
]

Value type validation

The API checks whether the type of value chosen for the field is correct.

var tasks = new GlideQuery('incident')
    .where('state', '8') // invalid value for the choice field 'state'
    .select('number')
    .forEach(function (g) {
        gs.info(g.number);
    });

Returns:

NiceError: [2024-07-22T12:56:51.428Z]: Unable to match value '8' with field 'state' in table 'incident'. Expecting type 'integer'

II. Be JavaScript (native JavaScript)

JavaScript objects bringing more familiarity to the way queries are made and reduce the learning curve. With GlideRecord we often have problems with the type of value returned:

var gr = new GlideRecord('sys_user');
gr.addQuery('first_name', 'Abel');
gr.query();

if (gr.next()) {
    gs.info(gr.first_name);
    gs.info(gr.firt_name === 'Abel');
    gs.info(typeof(gr.first_name));
}

Returns:

*** Script: Abel
*** Script: false
*** Script: object

As we can see Abel is different from Abel!
The reason for this confusion is that GlideRecord returns a Java object.

Using GlideQuery we don’t have this problem:

var user = new GlideQuery('sys_user')
    .where('first_name', 'Abel')
    .selectOne('first_name')
    .get(); // this method can throw error if no record is found

gs.info(user.first_name);
gs.info(user.first_name === 'Abel');
gs.info(typeof (user.first_name));

Returns:

*** Script: Abel
*** Script: true
*** Script: string

III. Be Expressive

Do more with less code! Simplify writing your code!

Performance

Using GlideQuery can increase processing time by around 4%, mainly due to the conversion of the Java object to JavaScript. However, keep in mind that we will often do this conversion manually after a GlideRecord.

Stream x Optional

The API works together with 2 other APIs: Stream and Optional. If the query returns a single record, the API returns an Optional object. If it returns several records, the API returns an object of type Stream, which is similar to an array. These objects can be manipulated according to the methods of each API.

Practical Examples

Before we start with the examples, it is necessary to make 2 points clear

• The primary key of the table (in our case normally the sys_id) is always returned even if the request is not made in Select.
• Unlike GlideRecord, GlideQuery does not return all record fields. We need to inform the name of the fields we want to get:
  .selectOne([‘field_1’, ‘field_2’, ‘field_n’])

I. selectOne

It is very common that we only need 1 record and in these cases we use selectOne(). This method returns an object of type Optional and we need a terminal method to process it:

a) get

// Searches for the user and if it does not exist returns an error
var user = new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne('first_name')
    .get(); // this method can throw error if no record is found

gs.info(JSON.stringify(user, null, 4));

If success:

Script: {
    "first_name": "Fred",
    "sys_id": "5137153cc611227c000bbd1bd8cd2005"
}

If fail:

NiceError: [2024-07-21T22:28:48.274Z]: get() called on empty Optional: Unable to find a record with the following query:
GlideQuery<sys_user> [
  {
    "type": "where",
    "field": "last_name",
    "operator": "=",
    "value": "Luddyx",
    "whereClause": true
  }
]

b) orElse

Optional method used to handle queries that do not return any value.

var user = new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne(['first_name'])
    .orElse({  //Method in the Optional class to return a default value.
        first_name: 'Nobody'
    });

gs.info(JSON.stringify(user, null, 4));

If success:

Script: {
    "first_name": "Fred",
    "sys_id": "5137153cc611227c000bbd1bd8cd2005"
}

If fail:

Script: {
    "first_name": "Nobody"
}

c) ifPresent

var userExists = false;
new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne(['first_name'])
    .ifPresent(function (user) {
        gs.info(user.first_name + ' - ' + user.sys_id);
        userExists = true;
    });

gs.info(userExists);

If user exists:

*** Script: Fred - 5137153cc611227c000bbd1bd8cd2005
*** Script: true

If not:

*** Script: false

d) isPresent

var userExists = new GlideQuery('sys_user')
   .where('last_name', 'Luddy')
    . selectOne(['first_name'])
    .isPresent();

gs.info(userExists);

If user exists:

*** Script: true

If not:

*** Script: false

e) isEmpty

var userExists = new GlideQuery('sys_user')
   .where('last_name', 'Luddy')
    .selectOne(['first_name'])
    .isEmpty();

gs.info(userExists);

If user exists:

*** Script: false

If not:

*** Script: true

II. get

Returns a single record using sys_id

var user = new GlideQuery('sys_user')
    .get('62826bf03710200044e0bfc8bcbe5df1', ['first_name', 'last_name'])
    .orElse({
        first_name: 'Nobody',
        last_name: 'Nobody'
    });

gs.info(JSON.stringify(user, null, 4));

If user exists:

*** Script: {
    "sys_id": "62826bf03710200044e0bfc8bcbe5df1",
    "first_name": "Abel",
    "last_name": "Tuter"
}

If not:

*** Script: {
    "first_name": "Nobody",
    "last_name": "Nobody"
}

III. getBy

Returns a single record (even if there is more than 1 record) using the keys used as parameters.

var user = new GlideQuery('sys_user')
    .getBy({
        first_name: 'Fred',
        last_name: 'Luddy'
    }, ['city', 'active']) // select first_name, last_name, city, active
    .orElse({
        first_name: 'Nobody',
        last_name: 'Nobody',
        city: 'Nowhere',
        active: false
    });

gs.info(JSON.stringify(user, null, 4));

If user exists:

*** Script: {
    "first_name": "Fred",
    "last_name": "Luddy",
    "city": null,
    "active": true,
    "sys_id": "5137153cc611227c000bbd1bd8cd2005"
}

If not:

*** Script: {
    "first_name": "Nobody",
    "last_name": "Nobody",
    "city": "Nowhere",
    "active": false
}

IV. insert

The insert method needs an object as a parameter where each property must be the name of the field we want to fill. The insert returns an Optional with the data of the inserted object and the sys_id. We can also request extra fields for fields that are automatically populated:

var user = new GlideQuery('sys_user')
    .insert({  
        active: false,
        first_name: 'Thiago',
        last_name: 'Pereira',
    },['name'])
    .get()

gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "40f6e42147efc210cadcb60e316d43be",
    "active": false,
    "first_name": "Thiago",
    "last_name": "Pereira",
    "name": "Thiago Pereira"
}

V. update

The API has a method for when we want to update just one record. To use this method, the field used in the “where” must be the primary key and this way it updates just 1 record.

var user = new GlideQuery('sys_user')
    .where('sys_id', '40f6e42147efc210cadcb60e316d43be') //sys_id of the record created in the insert example
    .update({ email: 'thiago.pereira@example.com', active: true }, ['name'])
    .get();
gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "40f6e42147efc210cadcb60e316d43be",
    "email": "thiago.pereira@example.com",
    "active": true,
    "name": "Thiago Pereira"
}

VI. updateMultiple

var myQuery = new GlideQuery('sys_user')
    .where('active', false)
    .where('last_name', 'LIKE', 'Pereira') 
    .updateMultiple({ active: false });

gs.info(JSON.stringify(myQuery, null, 4));

If success:

*** Script: {
    "rowCount": 1
}

If fail:

*** Script: {
    "rowCount": 0
}

VII. insertOrUpdate

This method receives an object with the key(s) to perform the search. If one of the keys is a primary key (sys_id), it searches for the record and updates the other fields entered in the object. If no primary key is passed or the sys_id is not found, the method will create a new record. In this method we cannot select fields other than those passed in the object.

// Create a new record even though a user already exists with the values
var user = new GlideQuery('sys_user')
    .insertOrUpdate({
        first_name: 'Thiago',
        last_name: 'Pereira'
    })
    .orElse(null);
    
gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "5681fca1476fc210cadcb60e316d43b6",
    "first_name": "Thiago",
    "last_name": "Pereira"
}

// Update an existing record
var user = new GlideQuery('sys_user')
 .insertOrUpdate({
 sys_id: '40f6e42147efc210cadcb60e316d43be', //sys_id of the record created in the insert example
 first_name: 'Tiago',
 last_name: 'Pereira'})
 .orElse(null);

gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "40f6e42147efc210cadcb60e316d43be",
    "first_name": "Tiago",
    "last_name": "Pereira"
}

// Creates a new record as the sys_id does not exist
var user = new GlideQuery('sys_user')
    .insertOrUpdate({
        sys_id: 'xxxxxxxxxxxxxxxxxxxx',
        first_name: 'Thiago',
        last_name: 'Pereira2'})
    .orElse(null);
gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "50e338ed47afc210cadcb60e316d4364",
    "first_name": "Thiago",
    "last_name": "Pereira2"
}

VIII. deleteMultiple / del

There is no method to delete just one record. To do this we need to use deleteMultiple together with where() using a primary key. The method does not return any value.

var user = new  GlideQuery('sys_user')
    .where('last_name', "CONTAINS", 'Pereira2')
    .deleteMultiple();

IX. whereNotNull

Note: We will talk about dot walking later.

new GlideQuery('sys_user')
    .whereNotNull('company')
    .whereNotNull('company.city')
    .select('name', 'company.city')
    .forEach(function (user) {
        gs.info(user.name + ' works in ' + user.company.city)
    });

Returns:

*** Script: Lucius Bagnoli works in Tokyo
*** Script: Melinda Carleton works in London
*** Script: Jewel Agresta works in London
*** Script: Christian Marnell works in Prague
*** Script: Naomi Greenly works in London
*** Script: Jess Assad works in Tokyo
etc.

X. limit

new GlideQuery('sys_user')
    .whereNotNull('company')
    .whereNotNull('company.city')
    .limit(2)
    .select('name', 'company.city')
    .forEach(function (user) {
        gs.info(user.name + ' works in ' + user.company.city)
    });

Returns:

*** Script: Mildred Gallegas works in Rome
*** Script: Elisa Gracely works in Rome

XI. select

We often need queries that return several records and in these cases we use select(). This method returns an object of type Stream and we need a terminal method to process it:

a) forEach

var arrIncidens = [];
var incidents = new GlideQuery('incident')
    .where('state', 2)
    .limit(2)
    .select('number')
    .forEach(function (inc) {
        gs.info(inc.number + ' - ' + inc.sys_id);
        arrIncidens.push(inc.sys_id);
    });

if (arrIncidens.length==0)
    gs.info('Not found.')

If success:

*** Script: INC0000025 - 46f09e75a9fe198100f4ffd8d366d17b
*** Script: INC0000029 - 46f67787a9fe198101e06dfcf3a78e99

If fail:

*** Script: Not found.

b) toArray

Returns an array containing the items from a Stream. The method needs a parameter that is the maximum size of the array, the limit being 100.

var users = new global.GlideQuery('sys_user')
    .limit(20)
    .select('first_name', 'last_name')
    .toArray(2); // max number of items to return in the array

gs.info(JSON.stringify(users, null, 4));

Returns:

*** Script: [
    {
        "first_name": "survey",
        "last_name": "user",
        "sys_id": "005d500b536073005e0addeeff7b12f4"
    },
    {
        "first_name": "Lucius",
        "last_name": "Bagnoli",
        "sys_id": "02826bf03710200044e0bfc8bcbe5d3f"
    }
]

c) map

Used to transform each record in a Stream.

new GlideQuery('sys_user')
    .limit(3)
    .whereNotNull('first_name')
    .select('first_name')
    .map(function(user) {
        return user.first_name.toUpperCase();
    })
    .forEach(function(name) {
        gs.info(name);
    });

Returns:

*** Script: SURVEY
*** Script: LUCIUS
*** Script: JIMMIE

d) filter

Note: The filter in a Stream always occurs after the query has been executed. Therefore, whenever possible, we should make all possible filters before the select to avoid loss of performance.

var hasBadPassword = function(user) {
    return !user.user_password ||
        user.user_password.length < 10 ||
        user.user_password === user.last_password ||
        !/\d/.test(user.user_password) // no numbers
        ||
        !/[a-z]/.test(user.user_password) // no lowercase letters
        ||
        !/[A-Z]/.test(user.user_password); // no uppercase letters
};

new GlideQuery('sys_user')
    .where('sys_id', 'STARTSWITH', '3')
    .select('name', 'email', 'user_password', 'last_password')
    .filter(hasBadPassword)
    .forEach(function(user) {
        gs.info(user.name + ' - ' + user.sys_id)
    });

Returns:

*** Script: Patty Bernasconi - 3682abf03710200044e0bfc8bcbe5d17
*** Script: Veronica Achorn - 39826bf03710200044e0bfc8bcbe5d1f
*** Script: Jessie Barkle - 3a82abf03710200044e0bfc8bcbe5d10

e) limit

Both the GlideQuery and Stream APIs have the limit() method. In GlideQuery it is executed before the execution of the query, which is more performant. For this reason, whenever possible, we should use the limit before executing the select.

new GlideQuery('task')
    .orderBy('priority')
    .limit(3) // Good: calling GlideQuery's limit method
    .select('assigned_to', 'priority', 'description')
    //.limit(3) // Bad: calling Stream's limit method
    .forEach(function(myTask) {
        gs.info(myTask.priority + ' - ' + myTask.assigned_to)
    });

Returns:

*** Script: 1 - 5137153cc611227c000bbd1bd8cd2007
*** Script: 1 - 681b365ec0a80164000fb0b05854a0cd
*** Script: 1 - 5137153cc611227c000bbd1bd8cd2007

f) find

Returns the first item found in the Stream according to the given condition. Important notes:

• Returns an Optional, which may be empty if no item is found.
• The first item in the Stream is returned if no condition is entered.

var hasBadPassword = function(user) {
    return !user.user_password ||
        user.user_password.length < 10 ||
        user.user_password === user.last_password ||
        !/\d/.test(user.user_password) || // no numbers
        !/[a-z]/.test(user.user_password) || // no lowercase letters
        !/[A-Z]/.test(user.user_password); // no uppercase letters
};
var disableUser = function(user) {
    var myGQ = new GlideQuery('sys_user')
        .where('sys_id', user.sys_id)
        .update({
            active: false
        }, ['user_name'])
        .get();
        gs.info(JSON.stringify(myGQ, null, 4));
};
var myUsers = new GlideQuery('sys_user')
    .select('name', 'email', 'user_password', 'last_password')
    .find(hasBadPassword)
    .ifPresent(disableUser);

Returns:

*** Script: {
    "sys_id": "0e826bf03710200044e0bfc8bcbe5d45",
    "active": false,
    "user_name": "ross.spurger"
}

g) reduce

var longestFirstName = new global.GlideQuery('sys_user')
   .whereNotNull('first_name')
   .select('first_name')
   .reduce(function (acc, cur) {
       return (cur.first_name.length > acc.length) ? cur.first_name : acc;
       }, '');

gs.info(JSON.stringify(longestFirstName));

Returns:

*** Script: "Sitemap Scheduler User"

h) Every

Executes a function on each item in the Stream. If it returns true for all items in the Stream, the method returns true, otherwise it returns false.

var numToCompare = 10;
//var numToCompare = 1000;

var hasOnlyShortDescriptions = new global.GlideQuery('task')
    .whereNotNull('description')
    .select('description')
    .every(function(t) {
        return t.description.length < numToCompare;
    });

gs.info(hasOnlyShortDescriptions);

If numToCompare  is 10, returns:

*** Script: false

If numToCompare  is 1000, returns:

*** Script: true

i) some

Executes a function on each item in the Stream. If it returns true for at least one item in the Stream, the method returns true, otherwise it returns false.

var numToCompare = 10;
//var numToCompare = 1000;

var hasLongDescriptions = new global.GlideQuery('task')
   .whereNotNull('description')
   .select('description')
   .some(function (t) { 
      return t.description.length > numToCompare; 
   });

gs.info(hasLongDescriptions);

If numToCompare  is 10, returns:

*** Script: true

If numToCompare  is 1000, returns:

*** Script: false

j) flatMap

FlatMap is very similar to map but with 2 differences:

1.  The function passed to flatMap must return a Stream.
2. The flatMap manipulates (unwraps/flattens) the returned Stream so that the “parent” method can return the result.

var records = new global.GlideQuery('sys_user')
    .where('last_login', '>', '2015-12-31')
    .select('first_name', 'last_name')
    .flatMap(function(u) {
        return new global.GlideQuery('task')
            .where('closed_by', u.sys_id)
            .where('short_description', 'Prepare for shipment')
            .select('closed_at', 'short_description')
            .map(function(t) {
                return {
                    first_name: u.first_name,
                    last_name: u.last_name,
                    short_description: t.short_description,
                    closed_at: t.closed_at
                };
            });
    })
    .toArray(50);

gs.info(JSON.stringify(records, null, 4));

Returns:

*** Script: [
    {
        "first_name": "Thiago",
        "last_name": "Pereira",
        "short_description": "Prepare for shipment",
        "closed_at": "2021-10-04 13:43:48"
    },
    {
        "first_name": "Thiago",
        "last_name": "Pereira",
        "short_description": "Prepare for shipment",
        "closed_at": "2021-10-04 13:40:22"
    },
    {
        "first_name": "Thiago",
        "last_name": "Pereira",
        "short_description": "Prepare for shipment",
        "closed_at": "2021-10-04 13:42:14"
    }
]

XII. parse

Similar to GlideRecord’s addEcodedQuery but does not accept all operators. Currently only the following operators are supported:

var myTask = new GlideQuery.parse('task', 'active=true^short_descriptionLIKEthiago^ORDERBYpriority')
    .limit(10)
    .select('short_description', 'priority')
    .toArray(10); // 10 is the max number of items to return in the array

gs.info(JSON.stringify(myTask, null, 4));

Returns:

*** Script: [
    {
        "short_description": "thiago teste update 2",
        "priority": 1,
        "sys_id": "8b2c540b47ce0610cadcb60e316d4377"
    }
]

XIII. orderBy / orderByDesc

var users = new global.GlideQuery('sys_user')
    .limit(3)
    .whereNotNull('first_name')
    .orderBy('first_name')
    //.orderByDesc('first_name')
    .select('first_name', 'last_name')
    .toArray(3); 

gs.info(JSON.stringify(users, null, 4));

Returns:

*** Script: [
    {
        "first_name": "Abel",
        "last_name": "Tuter",
        "sys_id": "62826bf03710200044e0bfc8bcbe5df1"
    },
    {
        "first_name": "Abraham",
        "last_name": "Lincoln",
        "sys_id": "a8f98bb0eb32010045e1a5115206fe3a"
    },
    {
        "first_name": "Adela",
        "last_name": "Cervantsz",
        "sys_id": "0a826bf03710200044e0bfc8bcbe5d7a"
    }
]

XIV. withAcls

It works in the same way as GlideRecordSecure, forcing the use of ACLs in queries.

var users = new GlideQuery('sys_user')
    .withAcls()
    .limit(4)
    .orderByDesc('first_name')
    .select('first_name')
    .toArray(4);

gs.info(JSON.stringify(users, null, 4));

XV. disableAutoSysFields

new GlideQuery('task')
    .disableAutoSysFields()
    .insert({ description: 'example', priority: 1 });

XVI. forceUpdate

Forces an update to the registry. Useful, for example, when we want some BR to be executed.

new GlideQuery('task')
    .forceUpdate()
    .where('sys_id', 'd71b3b41c0a8016700a8ef040791e72a')
    .update()

XVII. disableWorkflow

new GlideQuery('sys_user')
    .disableWorkflow() // ignore business rules
    .where('email', 'bob@example.com')
    .updateMultiple({ active: false });

XVIII. Dot Walking

var tokyoEmployee = new GlideQuery('sys_user')
    .where('company.city', 'Tokyo')
    .selectOne('name', 'department.id')
    .get();
gs.info(JSON.stringify(tokyoEmployee, null, 4));

Returns:

*** Script: {
    "name": "Lucius Bagnoli",
    "department": {
        "id": "0023"
    },
    "sys_id": "02826bf03710200044e0bfc8bcbe5d3f"
}

XIX. Field Flags

Some fields support metadata. The most common case is the “display value”. We use the “$” symbol after the field name and specify the desired metadata. Metadata supported so far:

1. $DISPLAY = getDisplayValue
2. $CURRENCY_CODE = getCurrencyCode
3. $CURRENCY_DISPLAY = getCurrencyDisplayValue
4. $CURRENCY_STRING = getCurrencyString

new GlideQuery('sys_user')
    .whereNotNull('company')
    .limit(3)
    .select('company', 'company$DISPLAY')
    .forEach(function(user) {
        gs.info(user.company$DISPLAY + ' - ' + user.company);
    });

Returns:

*** Script: ACME Italy - 187d13f03710200044e0bfc8bcbe5df2
*** Script: ACME Italy - 187d13f03710200044e0bfc8bcbe5df2
*** Script: ACME Italy - 187d13f03710200044e0bfc8bcbe5df2

XX. aggregate

Used when we want to make aggregations. GlideQuery also has methods for this and they are often easier to use than GlideAggregate.

a) count

Using ‘normal’ GlideAggregate

var usersGa = new GlideAggregate('sys_user');
usersGa.addAggregate('COUNT');
usersGa.query();
usersGa.next();
gs.info(typeof (usersGa.getAggregate('COUNT')));
var userCount = parseInt(usersGa.getAggregate('COUNT'));
gs.info(typeof (userCount));
gs.info(userCount);

Returns:

*** Script: string
*** Script: number
*** Script: 629

Using GlideQuery

var userCount = new GlideQuery('sys_user').count();
gs.info(typeof(userCount));
gs.info(userCount);

Returns:

*** Script: number
*** Script: 629

Note that in addition to using fewer lines of code, GlideQuey returns a number while GlideAggregate returns a string.

b) avg

var faults = new GlideQuery('cmdb_ci')
    .avg('sys_mod_count')
    .orElse(0);
gs.info(faults);

Returns:

*** Script: 7.533

c) max

var faults = new GlideQuery('cmdb_ci')    
    .max('sys_mod_count')
    .orElse(0);
gs.info(faults);

Returns:

*** Script: 172

d) min

var faults = new GlideQuery('cmdb_ci')    
    .min('sys_mod_count')
    .orElse(0);
gs.info(faults);

Returns:

*** Script: 0

e) sum

var totalFaults = new GlideQuery('cmdb_ci')
    .sum('sys_mod_count')
    .orElse(0);
gs.info(totalFaults);

Returns:

*** Script: 20972

f) groupBy

var taskCount = new GlideQuery('incident')
    .aggregate('count')
    .groupBy('state')
    .select()
    .forEach(function (g) {
        gs.info(JSON.stringify(g, null, 4));
    });

Returns:

*** Script: {
    "group": {
        "state": 1
    },
    "count": 13
}
*** Script: {
    "group": {
        "state": 2
    },
    "count": 19
}
etc.

g) aggregate

var taskCount = new GlideQuery('task')
    .groupBy('contact_type')
    .aggregate('avg', 'reassignment_count')
    .select()
    .forEach(function (g) {
        gs.info(JSON.stringify(g, null, 4));
    });

Returns:

*** Script: {
    "group": {
        "contact_type": ""
    },
    "avg": {
        "reassignment_count": 0.0033
    }
}
*** Script: {
    "group": {
        "contact_type": "email"
    },
    "avg": {
        "reassignment_count": 1
    }
}
etc.

h) having

new GlideQuery('core_company')
    .aggregate('sum', 'market_cap')
    .groupBy('country')
    .having('sum', 'market_cap', '>', 0)
    .select()
    .forEach(function (g) {
        gs.info('Total market cap of ' + g.group.country + ': ' + g.sum.market_cap);
    });

Returns:

*** Script: Total market cap of : 48930000000
*** Script: Total market cap of USA: 5230000000

The post GlideQuery Cheat Sheet appeared first on ServiceNow Guru.

• You need a set of information from a table.
• That information should be filtered based on information from another table that is related…but the relationship/reference that connects them is pointing the wrong direction to use dot-walking.
• You do a query of the first table, loop through and assemble the attributes you want to use as additional filtering (in an array typically).
• Use the results as part of the conditions on the query against another table.

Something like this:

var active_inc_array = []; 
var IncGr = new GlideRecord("incident"); 
IncGr.addEncodedQuery('active=true^problem_idISNOTEMPTY'); 
IncGr.query(); 
while(IncGr.next()) { 
     active_inc_array.push(IncGr.problem_id + ""); 
} 
var ProbGr = new GlideRecord("problem"); 
ProbGr.addQuery("active","false"); 
ProbGr.addQuery("sys_id", "IN", active_inc_array.join()); 
ProbGr.query();

In the above we ultimately want information from the problem table, however, we want to use incident information to filter. To achieve this we first query the incident table for all active incidents that reference a problem record, create an array of the problem record sys_id’s then use that problem sys_id array as filtering against the problem table along with the active=false.  The idea here being we probably shouldn’t have active incidents if the problem the incident is related too is not active.

While the above works fine, there is a better, more performant, cleaner way of accomplishing the same result. That way is by using a join on you GlideRecord query. The word join is a bit misleading as it is not really a join as you might have experienced when building/using a database view.  The join is really a subquery as it helps you filter results from one table based on data in another table BUT you can NOT access data in that other table as part of the record set returned from your GliderRecord call.

Lets take a look at our previous example and then look at how we can do it better. First, our ‘brute force’ method again:

Brute Force

var active_inc_array = [];
var IncGr = new GlideRecord("incident");
IncGr.addEncodedQuery('active=true^problem_idISNOTEMPTY'); 
IncGr.query();
while(IncGr.next()) {
     active_inc_array.push(IncGr.problem_id + "");
}
var ProbGr = new GlideRecord("problem");
ProbGr.addQuery("active","false");
ProbGr.addQuery("sys_id", "IN", active_inc_array.join());
ProbGr.query();

To do this cleaner/better we can use the join functionality that is part of GlideRecord. GlideRecord presents a couple of ways to use join functionality.  First is through the use of the addJoinQuery() function. The addJoinQuery() function takes a table name as it’s parameter. In our case ‘incident’. Take a look at our use case implemented using addJoinQuery():

GlideRecord.addJoinQuery()

var ProbGr = new GlideRecord('problem');
var IncJoin = ProbGr.addJoinQuery('incident');
ProbGr.addQuery('active', 'false');
IncJoin.addCondition('active', 'true');
ProbGr.query();

Note a couple of things:

• You must assign the result of the addJoinQuery() to a variable as you then need to reference that variable later for the condition.
• There has to be a reference on the subquery table (in our example incident, through the field problem_id) to the ‘main’ table (in our example problem).

The above works but also has a bit of black box ‘magic’ involved as you don’t explicitly get to define how the two tables are connected/’joined’. To remove that ambiguity you can also do the same using an encoded query string:

Encoded Query

var query = "active=false^JOINproblem.sys_id=incident.problem_id!active=true"
var ProbGr = new GlideRecord('problem'); 
ProbGr.addEncodedQuery(query); 
ProbGr.query();

Lets take a look at the encoded query string in line 1 a bit closer as that is where the fun is:) Deconstructing the string:

• active=false: conditions on the target table, in our case problem
• JOINproblem.sys_id=incident.problem_id:  explicitly definition of the criteria that connects records from the target table (problem) to the subquery table (incident) using the JOIN keyword
• !active=true: condition on the subquery table, in our case incident, following the “!” delimter

The one limitation that we have run in to is that the condition on the subquery table only allows 1 condition to be applied (in our case active=true).

The above example is obviously a bit trivial but for larger data sets I’ve seen a noticeable difference in performance between the brute force method and using GlideRecord JOINs. Given how often I have run in to the need to use a subquery, I’ve found the usage of the GlideRecord JOIN functionality a much more efficient alternative to have in my tool box. Hopefully you will as well!

The post Leveraging Joins in GlideRecord Queries appeared first on ServiceNow Guru.

While the content of this post isn’t new information (additional examples can be found on the Service-now wiki), my aim is to provide a single page of information containing some common examples of these methods as a reference. This is an excellent page to keep bookmarked!

var gr = new GlideRecord('incident'); //Indicate the table to query from
//The 'addQuery' line allows you to restrict the query to the field/value pairs specified (optional)
//gr.addQuery('active', true);
gr.query(); //Execute the query
while (gr.next()) { //While the recordset contains records, iterate through them
//Do something with the records returned
if(gr.category == 'software'){
gs.log('Category is ' + gr.category);
}
}

var gr = new GlideRecord('sys_user');
gr.addQuery('name', 'Joe Employee');
gr.query(myCallbackFunction); //Execute the query with callback function//After the server returns the query recordset, continue here
function myCallbackFunction(gr){
while (gr.next()) { //While the recordset contains records, iterate through them
alert(gr.user_name);
}
}

var gr = new GlideRecord('incident');
gr.get(sys_id_of_record_here);
//Do something with the record returned
if(gr.category == 'software'){
gs.log('Category is ' + gr.category);
}

//Find the first active incident record
var gr = new GlideRecord('incident');
if(gr.get('active', true)){
//Do something with the record returned
gs.log('Category is ' + gr.category);
}

var caller = current.caller_id.getRefRecord(); //Returns the GlideRecord for the value populated in the 'caller_id' field
caller.email = 'test@test.com';
caller.update();

//Find all incidents with a priority of 1 or 2
var gr = new GlideRecord('incident');
var grOR = gr.addQuery('priority', 1);
grOR.addOrCondition('priority', 2);
gr.query();
while (gr.next()) {
//Do something with the records returned
if(gr.category == 'software'){
gs.log('Category is ' + gr.category);
}
}

//Find all incidents with a priority of 1 or 2
var gr = new GlideRecord('incident');
gr.addQuery('priority', 1).addOrCondition('priority', 2);
gr.query();

//Create a new Incident record and populate the fields with the values below
var gr = new GlideRecord('incident');
gr.initialize();
gr.short_description = 'Network problem';
gr.category = 'software';
gr.caller_id.setDisplayValue('Joe Employee');
gr.insert();

//Find all active incident records and make them inactive
var gr = new GlideRecord('incident');
gr.addQuery('active',true);
gr.query();
while (gr.next()) {
gr.active = false;
gr.update();
}

//Find all inactive incident records and delete them one-by-one
var gr = new GlideRecord('incident');
gr.addQuery('active',false);
gr.query();
while (gr.next()) {
//Delete each record in the query result set
gr.deleteRecord();
}

//Find all inactive incidents and delete them all at once
var gr = new GlideRecord('incident');
gr.addQuery('active', false);
gr.deleteMultiple(); //Deletes all records in the record set

//Find all active incidents where the category is software or hardware
var gr = new GlideRecord('incident');
var strQuery = 'active=true';
strQuery = strQuery + '^category=software';
strQuery = strQuery + '^ORcategory=hardware';
gr.addEncodedQuery(strQuery);
gr.query();

//Find all active incidents and log a count of records to the system log
var gr = new GlideAggregate('incident');
gr.addQuery('active', true);
gr.addAggregate('COUNT');
gr.query();
var incidents = 0;
if (gr.next()){
incidents = gr.getAggregate('COUNT');
gs.log('Active incident count: ' + incidents);
}

//Find all active incidents and order the results ascending by category then descending by created date
var gr = new GlideRecord('incident');
gr.addQuery('active', true);
gr.orderBy('category');
gr.orderByDesc('sys_created_on');
gr.query();

//Find all incidents where the Short Description is empty
var gr = new GlideRecord('incident');
gr.addNullQuery('short_description');
gr.query();

//Find all incidents where the Short Description is not empty
var gr = new GlideRecord('incident');
gr.addNotNullQuery('short_description');
gr.query();

//Log the number of records returned by the query
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
gs.log('Incident count: ' + gr.getRowCount());

//Log the number of records returned by the query
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
alert('Incident count: ' + gr.rows.length);

//Find the last 10 incidents created
var gr = new GlideRecord('incident');
gr.orderByDesc('sys_created_on');
gr.setLimit(10);
gr.query();

//Find the last 10 incidents created
var gr = new GlideRecord('incident');
gr.orderByDesc('sys_created_on');
gr.chooseWindow(10, 20);
gr.query();

//Change the category of all 'software' incidents to 'hardware' without triggering business rules on updated records
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
while(gr.next()){
gr.category = 'hardware';
gr.setWorkflow(false);
gr.update();
}

//Change the category of all 'software' incidents to 'hardware' without updating sys fields
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
while(gr.next()){
gr.category = 'hardware';
gr.autoSysFields(false);
gr.update();
}

//Force an update to all User records without changing field values
var gr = new GlideRecord('sys_user');
gr.query();
while (gr.next()) {
gr.setWorkflow(false); //Do not run business rules
gr.autoSysFields(false); //Do not update system fields
gr.setForceUpdate(true); //Force the update
gr.update();
}

The post GlideRecord Query Cheat Sheet appeared first on ServiceNow Guru.

Using this little helper I put together the other day saved me some time and hassle of tracking down the table names and filters to get the right query. It’s a simple little script that makes life as an admin just a little bit easier.

To set it up in your instance go to System UI -> UI Context Menus and open a new record. The other values should be as follows:

Table: Global
Menu: List Header
Type: Action
Name: Get GlideRecord Query
Condition: gs.hasRole(‘admin’)
Order: 2000
Action script:

// Check for a query from the related list
var rel_list_prefix = '';
try{
   if(g_form != undefined){
      var field_name = g_list.getRelated().split('.')[1];
      if(field_name != undefined){
         var sys_id = g_form.getUniqueValue();
         rel_list_prefix = field_name + "=" + sys_id + "^";
      }
   }
} catch(e) {}

// Generate the query
var query = "var gr = new GlideRecord('" + g_list.getTableName() + "');\n";
query += "gr.addQuery('" + rel_list_prefix + g_list.getQuery() + "');\n";
query += "gr.query();\n";
query += "while(gr.next()) {\n";
query += " \n";
query += "}";
alert(query);

Now that you’ve got this, from any List you can right-click on the header and the bottom option will be “Get GlideRecord Query” and you can copy the resulting code. It’s nothing complicated, but can still save a bit of time.

The key to making this work is the g_list object that has the details for the list that you’re on. It’s documented fairly well on the ServiceNow Wiki and if you haven’t seen it before I’d recommend glancing at what options are available.

The post Generate a GlideRecord Query for a List appeared first on ServiceNow Guru.

Identifying modified or changed fields in Client scripts

When looking at a form in Service-now, it’s usually pretty easy to identify the fields that have changed since the form loaded (as seen in the screen shot above). The green field indicators give a simple visual cue to the user. Behind the UI, the system is taking cues as well and firing off ‘onChange’ events and setting field parameters as a result. When you write on ‘onChange’ client script or a UI policy, you can tie into the events and take actions as a result. If at all possible, you should stick to ‘onChange’ client scripts or UI policies.
There are some special situations (typically on submission of a record) where you don’t have a special event that tells you something changed. In an ‘onSubmit’ scenario, you have to check for these changes yourself. A great example of this can be used is the ‘Dirty form’ navigation property (glide.ui.dirty_form_support) that can be turned on to display a message to users if they navigate away from a ‘dirty’ or changed form without first saving the record.

function onSubmit() {
  if(g_form.modified){
    return confirm("Values on the form have changed.\nDo you really want to save this record?");
  }
}

function onSubmit() {
  var field1 = g_form.getControl('caller_id'); //Get the 'Caller' field control
  var field2 = g_form.getControl('short_description'); //Get the 'Short description' field control
 
  //See if the 'changed' attribute on either field is true
  if(field1.changed || field2.changed){
    return confirm("Values on the form have changed.\nDo you really want to save this record?");
  }
}

var el_id = g_form.getControl("requested_for").id;
var orig_val = gel("sys_original."+ el_id).value;
var new_val = g_form.getValue("requested_for");
if(orig_val != new_val){
   alert("It's changed");
}

if($$('.changed').length > 0){
   return confirm("Values on the form have changed.\nDo you really want to save this record?");
}

Identifying modified or changed fields in Business rules

Business rules are server-side scripts, so there’s no form, no green indicator, and no ‘onChange’ event to tell you what happened. In these scenarios, business rules just care about what’s on its way to the record in the database.
Service-now includes some simple convenience methods for identifying changes to a record or field in a business rule. You may have seen these used in the ‘Condition’ field on many of the out-of-box business rules. All of the methods below return true or false based on changes to the ‘current’ record.

Record change: current.changes();
Field change: current.YOUR_FIELD_NAME_HERE.changes();
Field changes TO a value: current.YOUR_FIELD_NAME_HERE.changesTo(VALUE);
Field changes FROM a value: current.YOUR_FIELD_NAME_HERE.changesFrom(VALUE);

But what if you don’t know in advance what might change? What if you just need to return a list of changed fields or values? This might be one field, or 30 fields! I recently discovered the ‘GlideScriptRecordUtil’ class can get this type of information for you. You can use the following methods in a ‘before’ or ‘after’ business rule.

var gru = GlideScriptRecordUtil.get(current);
gru.getChangedFields(); //Returns an arrayList of changed field elements with friendly names
gru.getChangedFieldNames(); //Returns an arrayList of changed field elements with database names
gru.getChanges(); //Returns an arrayList of all change values from changed fields

//Display an information message for each change to the record
var gru = GlideScriptRecordUtil.get(current);
var changedFields = gru.getChangedFields(); //Get changed fields with friendly names
var changedValues = gru.getChanges(); //Get changed field values
//Convert to JavaScript Arrays
gs.include('j2js');
changedFields = j2js(changedFields);
changedValues = j2js(changedValues);

//Process the changed fields
for(var i = 0; i < changedValues.length; i++){
   var val = changedValues[i];
   if(val.getED().getType() == -1 && val.getJournalEntry(1)){
      //Print out last journal entry for journal fields
      gs.addInfoMessage(val.getJournalEntry(1));
   }
   else{
      //Print out changed field and value for everything else
      gs.addInfoMessage(changedFields[i] + ': ' + val.getDisplayValue());
   }
}

Printing all record changes in an email notification

One common use case I’ve seen for collecting all record changes in an update is to send all of those changes in an email notification. Without the use of the ‘GlideScriptRecordUtil’ methods described above, this really wasn’t practical. Here’s a solution I’ve come up with that allows you to accomplish this…

The first part is gathering these changes when you trigger your email notification event. Because the changes need to be captured at the time the record is updated or inserted, this needs to happen in a business rule. This script example collects all changed fields on an incident record and passes them as an event parameter to the ‘Incident Assigned to my Group’ notification.

//Collect all changed fields
var gru = GlideScriptRecordUtil.get(current);
var fields = gru.getChangedFieldNames(); //Get changed fields with database names
//Convert to JavaScript Array
gs.include('j2js');
fields = j2js(fields);

if (!current.assignment_group.nil() && current.assignment_group.changes()) {
   gs.eventQueue("incident.assigned.to.group", current, fields , '');
}

Once you’ve passed the event (with the changed fields parameter) to your notification, you can set up a mail script to iterate through the changed fields and print the results to the outgoing email.

Changed fields:
<mail_script>
//Process the changed fields
var fields = event.parm1.split(',');
for(x in fields){
   //Get the field label, value, and type
   var field = fields[x];
   var fieldLabel = eval('current.' + field + '.getLabel()');
   var fieldVal = eval('current.' + field+ '.getDisplayValue()');
   var fieldType = eval('current.' + field + '.getED().getType()');

   if(fieldType == -1 && eval('current.' + field + '.getJournalEntry(1)')){
      //Print out last journal entry for journal fields
      template.print(eval('current.' + field + '.getJournalEntry(1)'));
   }
   else{
      //Print out changed field and value for everything else
      template.print(fieldLabel + ': ' + fieldVal + '\n');
   }
}
</mail_script>

The result of this mail script will look something like this…

The post Checking for Modified or Changed Fields in Script appeared first on ServiceNow Guru.

var gr = new GlideAggregate('cmdb_ci_server'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy('os'); //Group aggregate by the 'os' field
gr.query();
while(gr.next()){
   var osCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
   gs.print('Distinct operating system: ' + gr.os + ': ' + osCount);
}

Running the script above from the ‘Scripts – Background’ module in your system will produce a result similar to this…

Output CIs by Class

Here’s another method that I’ve used before to give me a quick and dirty output of CIs in a system by class. Use this with extreme caution! It can be run directly from the ‘Scripts – Background’ module.

gs.sql('SELECT sys_class_name,count(*) AS total FROM cmdb_ci GROUP BY sys_class_name ORDER BY total DESC');

The post Distinct GlideRecord Query appeared first on ServiceNow Guru.

var tbl = 'cmdb_ci'; //Query from this table
var returnNum = 5; //Return this number of records in the array
var answer = new Array();

//Set up the GlideRecord query
var rec = new GlideRecord(tbl);
rec.query();
if(rec.hasNext()){
   //Get the number of records queried
   var numRecs = rec.getRowCount();
   //Ensure number of records is not less than number to return
   if(numRecs < returnNum){
     returnNum = numRecs;
   }
   //Return 'returnNum' number of random records in an array
   for(i=0; i < returnNum; i++){
      //Get a random number between 0 and 'numRecs'
      var randomNumber = Math.floor(Math.random()*numRecs);
      //Set the row number to 'randomNumber'
      rec.setLocation(randomNumber);
      //Add the random record to the array
      answer.push(rec.getDisplayValue().toString());
   }
}
gs.log(answer.join()); //Log the output
return answer; //Return the array

The post Returning Random Records Using GlideRecord appeared first on ServiceNow Guru.

//Make sure current changes are saved...
current.update();

//Copy the variable set
copyVS();
function copyVS() {
//Get the current sys_id value for querying
var vsID = current.sys_id.toString();
//Initialize new variable set for insertion
var newVS = current;
//Override the variable set name with a new name
newVS.name = current.name + ' (COPY)';
current.insert();

//Copy associated variables and client scripts
copyVar(vsID);
copyCS(vsID);
gs.addInfoMessage('Variable set ' + newVS.name + ' created.');
action.setRedirectURL(newVS);
}
function copyVar(vsID) {
//Copy the associated variables
var vars = new GlideRecord('item_option_new');
vars.addQuery('variable_set', vsID);
vars.query();
while(vars.next()){
//Get the current sys_id value for querying
var varID = vars.sys_id.toString();
var newVar = vars;
newVar.variable_set = current.sys_id;
vars.insert();
copyChoice(varID, newVar.sys_id.toString());
}
}
function copyCS(vsID) {
//Copy the associated catalog client scripts
var scripts = new GlideRecord('catalog_script_client');
scripts.addQuery('variable_set', vsID);
scripts.query();
while(scripts.next()){
var newCS = scripts;
newCS.variable_set = current.sys_id;
scripts.insert();
}
}
function copyChoice(varID, newVarID) {
//Copy the associated question choices for choice variables
var choices = new GlideRecord('question_choice');
choices.addQuery('question', varID);
choices.query();
while(choices.next()){
var newChoice = choices;
newChoice.question = newVarID;
choices.insert();
}
}

The post Copying a Variable Set appeared first on ServiceNow Guru.