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.

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.

But what if you need to do both? The classic case is when you want to click a button to make an update to a record, but only if the user has provided the correct input first. An example would be a ‘Reopen Incident’ button that changes the state on an incident record from ‘Resolved’ to ‘Active’. Usually you want to require the user to provide some sort of comment or additional information explaining why they are reopening the ticket. The problem is that you don’t always want the ‘Comments’ field to be mandatory so the validation needs to happen at the time the ‘Reopen Incident’ button gets clicked. Validation of mandatory fields needs to happen client-side but the update to your record needs to happen server-side. How can you accomplish both of these things with a single UI Action? This article shows you how.

The basic format for using a Client Script and Business Rule in the same UI Action looks something like this…

//Client-side 'onclick' function
function runClientCode(){
if( == false){
return false; //Abort submission
}
//Call the UI Action and skip the 'onclick' function
gsftSubmit(null, g_form.getFormElement(), ''); //MUST call the 'Action name' set in this UI Action
}//Code that runs without 'onclick'
//Ensure call to server-side function with no browser errors
if(typeof window == 'undefined')
runBusRuleCode();//Server-side function
function runBusRuleCode(){
current. = ;
current.update();
gs.addInfoMessage('You did it!');
action.setRedirectURL(current);
}

So why does this work? I had to go to a Service-now developer to find out. The reason is that UI Actions can run scripts at two different times. The first time is when the UI Action gets clicked. When you define a ‘Client’ UI Action you also give that UI Action the name of a function in your ‘Script’ field to execute. This function has to be called explicitly (through the ‘onclick’ event) or it doesn’t run at all.
The second time is on the way to the server. This is how any UI Action without the ‘Client’ checkbox selected gets run. On the way to the server the entire UI Action script gets executed regardless of whether or not the ‘Client’ checkbox is checked. What this means is that any script you include in your UI Action that isn’t enclosed in a function will be run on the way to the server. The script above takes advantage of this fact by making a specific call to the ‘Client’ function, performing client-side validation, and then the UI Action calls itself if the client-side validation passes.
When the UI Action calls itself it bypasses the ‘onclick’ function because the button didn’t get clicked the second time. So the script continues to the first point where there is something to execute. At that point you can call your Server-side function! The only thing you need to be careful of is that you only call the Server-side function if the script isn’t running in the client anymore. That’s what the check in the middle does…and eliminates any browser errors saying that ‘current’ (or any other Server-side function or object) isn’t defined.

Here is a solution I’ve used in the past to give users the ability to reopen an incident record. The solution uses a UI Action button to check if the ‘Comments’ field has been filled in (this is the ‘Client-side’ portion). If the validation passes, then the incident record gets updated.

//Client-side 'onclick' function
function reopen(){
if(g_form.getValue('comments') == ''){
//Remove any existing field message, set comments mandatory, and show a new field message
g_form.hideFieldMsg('comments');
g_form.setMandatory('comments', true);
g_form.showFieldMsg('comments','Comments are mandatory when reopening an Incident.','error');
return false; //Abort submission
}
//Call the UI Action and skip the 'onclick' function
gsftSubmit(null, g_form.getFormElement(), 'reopen_incident'); //MUST call the 'Action name' set in this UI Action
}

//Code that runs without 'onclick'
//Ensure call to server-side function with no browser errors
if(typeof window == 'undefined')
serverReopen();

function serverReopen(){
//Set the 'State' to 'Active', update and reload the record
current.state = 2;
current.update();
gs.addInfoMessage('Incident ' + current.number + ' reopened.');
action.setRedirectURL(current);
}

The post Client & Server Code in One UI Action appeared first on ServiceNow Guru.

Where does ServiceNow store bookmark information anyway???

User bookmark information is stored in the ‘Bookmark [sys_ui_bookmark]’ table which can be accessed by navigating to ‘System Definition -> Bookmarks’ in the left nav. Here you can identify bookmarks per user along with their associated settings. Included in these settings is the ability to set global bookmarks, icons, links, etc. Generally it’s much more effective to set this information directly from the simple UI provided by ServiceNow in the edge pane itself. But what if you don’t like the default icon and color used when you create a bookmark? This requires some additional intervention via the back-end table structure.

As shown in the screenshot above, ServiceNow uses a combination of icon and color shortcodes to determine the appearance of a bookmark in the UI14 edge pane. The common defaults I’ve identified include the following…

• icon-view color-green (Module link default)
• icon-book color-blue-dark (List breadcrumb/filter default)

How to adjust the default icon look for bookmarks…

These shortcodes end up getting set in the ‘icon’ field (which is hidden by default) in the bookmark record. As far as I’ve been able to tell, if you want to change the defaults set here, you need to do so using a ‘before insert’ business rule on the ‘sys_ui_bookmark’ table. A script like this can be used to make these adjustments. Simply change the icon and color shortcodes within the ‘if’ statements accordingly!

//Change default bookmark color (green) to red
if(current.icon == 'icon-view color-green'){
current.icon = 'icon-view color-red';
}//Change default bookmark color (dark blue) to orange
if(current.icon == 'icon-book color-blue-dark'){
current.icon = 'icon-book color-orange';
}

//Change default record form (red) to green and icon from form to list
if(current.icon == 'icon-form color-red'){
current.icon = 'icon-list color-green';
}

The post Modifying UI14 Bookmark Appearance and Behavior appeared first on ServiceNow Guru.

The Solution…

At least in my mind, in order for this solution to be successful, it needs to meet a few criteria…

1. First and foremost, the user should not be notified of an approval request if they’ve already approved a record.
2. We need to maintain the history of the approval record for audit purposes and accurately reflect what happened to the redundant approval requests. This means that deleting or aborting record insertion is out of the question!
3. We cannot negatively impact the approval workflow by interrupting the normal approval process.

The first two criteria need to be met at the same time. Preventing the approval request could be done in a couple of different ways. One way would be to somehow manipulate the workflow activity to check if the user has previously approved. The drawback to this approach is that it requires hacking the workflow approval activities…which would be a significant upgrade risk, or adding custom code to every single approval activity in every single workflow…which would be a maintenance nightmare.

That leaves us with intercepting the creation/update of the approval records in a business rule. Using a ‘before’ insert/update business rule we can evaluate every ‘requested’ approval record, run a script to determine if the approval is for a user that has already approved this item, and then adjust the approval record by setting the approval state to ‘No Longer Required’…all before we trigger any update or notification to the approving user. We can also add some approval comments so that we can accurately reflect why the approval isn’t required anymore. Adding the following business rule to the ‘Approval [sysapproval_approver]’ table accomplishes that for us.

//Check to see if user has previously approved
approveDuplicateApproval();function approveDuplicateApproval(){
//Must have link to record being approved
if(current.document_id || current.sysapproval){
//Query for approval records for this user/record
var app = new GlideRecord('sysapproval_approver');
//Handle empty document_id and sysapproval fields
if(!current.document_id.nil()){
app.addQuery('document_id', current.document_id);
}
else if(!current.sysapproval.nil()){
app.addQuery('sysapproval', current.sysapproval);
}
app.addQuery('approver', current.approver);
app.addQuery('state', 'approved');
//Optionally restrict to current workflow
//app.addQuery('wf_activity.workflow_version', current.wf_activity.workflow_version);
app.query();
if(app.next()){
//If previous approval is found set this approval to 'approved'
current.state = 'not_required';
current.comments = "Approval marked by system as 'Not Longer Required' due to a previous approval on the same record by the same user.";
}
}
}

While the above script works great in manipulating the approval records on its own, it fails in the third criteria mentioned above…it can negatively impact the workflow in certain situations. This is because the workflow processing that happens after an approval status is changed isn’t seeing the true value of the approval request to process the workflow approval activity correctly. In my testing this couldn’t be done in a ‘before’ or ‘after’ business rule due to the timing of the updates. What is needed is to run the workflow checks one more time, and ensure that it happens after all of the above manipulation happens. The best way I could find to do this is through a separate ‘async’ business rule. The only downside to this async business rule is that it may not process immediately depending on the load on your system. Generally it should process within a few seconds though so for all practical intents and purposes it’s a non-issue.

// Run any workflows for our parent so they can check the approval states
runWorkflow_userApprove();function runWorkflow_userApprove() {
var id = current.sysapproval.nil() ? current.document_id : current.getValue('sysapproval');
var table = current.source_table.nil() ? 'task' : current.source_table;
if (id != null && table != null ) {
var gr = new GlideRecord(table);
if (gr.get(id)) {
new Workflow().runFlows(gr, 'update');
}
}
}

With the above solution in place, you should have created a very effective way to prevent the issue of redundant approval requests for the same user against the same record in ServiceNow.

The post Prevent Redundant Approval Requests in ServiceNow appeared first on ServiceNow Guru.

The following methods are designed for use in client-side scripting (primarily client scripts and UI policies). As such, they are used on standard forms and on catalog forms and can run on load or submit of a form, or on change of a field value.

Function/Method	Description	Usage
addInfoMessage(message)	Displays an informational message at the top of the form with a blue information icon and a light-blue background.	g_form.addInfoMessage('Form Info Message Text');
addErrorMessage(message)	Displays an error message at the top of the form with a red error icon and a light-red background.	g_form.addErrorMessage('Form Error Message Text');
clearOutputMessages	Hides ALL form info and error messages. There is no way to remove form info and error messages individually.	GlideUI.get().clearOutputMessages();
showFieldMsg(input, message, type, [scrollForm])	Displays either an informational or error message under the specified form field (either a control object or the name of the field). Type may be either "info" or "error." If the control or field is currently scrolled off the screen, it will be scrolled to. A global property (glide.ui.scroll_to_message_field) is available that controls automatic message scrolling when the form field is offscreen (scrolls the form to the control or field). Optional: Set scrollForm to false to prevent scrolling to the field message offscreen. Parameters: input - specifies the name of the field or control. message - the message to be displayed. type - error or info. scrollForm (optional) - true to scroll to message if offscreen, false to prevent this scrolling.	//Field info message g_form.showFieldMsg('priority','Priority is low.','info'); //Field error message g_form.showFieldMsg('impact','Priority is high!','error');
hideFieldMsg(input, [clearAll])	Hides info and error messages for a single field.	g_form.hideFieldMsg('impact', true);
hideAllFieldMsgs([type])	Hides all field info and error messages. Optional: Provide type to hide only "info" or "error" messages.	g_form.hideAllFieldMsgs();
flash(widgetName, color, count)	Flashes the specified color the specified number of times in the field. Parameters: widgetName - Specifies the element with (table name).(fieldname). color - RGB or CSS color count - How long the label will flash. use 2 for a 1-second flash use 0 for a 2-second flash use -2 for a 3-second flash use -4 for a 4-second flash	g_form.flash("incident.number", "#CC0000", -2);

Server-side UI Messages

Although messages will always be displayed client-side, they are often initiated from a server-side script like a business rule, record producer script, or script include. Messages initiated from server-side scripts can appear at the top of any form or list and are typically triggered by a database action such as a record insert, update, or delete.

Function/Method	Description	Usage
addInfoMessage(message)	Displays an informational message for the current session with a blue information icon and a light-blue background. Can also include HTML in the message! Note that I've replaced the greater than and less than symbols with brackets in the HTML usage example to get it to display correctly here. You'll need to change the brackets back to standard HTML to get it to work in your instance.	gs.addInfoMessage('Session Info Message Text'); //Info message with HTML formatting //Create the html contents of the information message var link = '[a href="incident.do?sys_id=' + current.sys_id + '" class="breadcrumb" ]' + current.number + '[/a]'; var message = 'Incident ' + link + ' has been created. '; message += 'Thank you for your submission.'; //Add the information message gs.addInfoMessage(message);
addErrorMessage(message)	Displays an error message for the current session with a red error icon and a light-red background. Can also include HTML in the message!	gs.addErrorMessage('Session Error Message Text');
flushMessages()	Clears any existing session info or error messages to prevent them from being displayed.	//Clear any session info or error messages gs.flushMessages();

UI Notifications

UI Notifications are an unusual class that can be used for refreshing elements such as the navigation pane or for displaying messages in response to changes in database tables. For more information about UI Notifications, check out the following articles:

• Refresh the Left Navigation Pane via Script
• Display Messages With UI Notifications

The post UI Info and Error Message Cheat Sheet appeared first on ServiceNow Guru.

Server-side refresh trigger

–For use in business rules, UI actions, etc.

// tell the UI to refresh the navigator
var notification = new UINotification('system_event');
notification.setAttribute('event', 'refresh_nav');
notification.send();

Client-side refresh trigger

–For use in client scripts, UI policies, etc.

getNavWindow().location = 'navigator_change.do';

For another way to use UI Notifications, take a look at our article Display Messages With UI Notifications.

The post Refresh the Left Navigation Pane via Script appeared first on ServiceNow Guru.

Add time based on schedule to current time

//Get a schedule by name to calculate duration
var schedRec = new GlideRecord('cmn_schedule');
schedRec.get('name', '8-5 weekdays');
if (typeof GlideSchedule != 'undefined')
   var sched = new GlideSchedule(schedRec.sys_id);
else
   var sched = new Packages.com.glide.schedules.Schedule(schedRec.sys_id);

//Get the current date/time in correct format for duration calculation
var currentDateTime = new GlideDateTime();
currentDateTime.setDisplayValue(gs.nowDateTime());

//Set the amount of time to add (in seconds)
var timeToAdd = 86400;
durToAdd = new GlideDuration(timeToAdd*1000);
var newDateTime = sched.add(currentDateTime, durToAdd, '');

//Set the 'requested_by_date' field to the new date/time
current.requested_by_date = newDateTime;

Add time based on schedule to current field value

//Get a schedule by name to calculate duration
var schedRec = new GlideRecord('cmn_schedule');
schedRec.get('name', '8-5 weekdays');
if (typeof GlideSchedule != 'undefined')
   var sched = new GlideSchedule(schedRec.sys_id);
else
   var sched = new Packages.com.glide.schedules.Schedule(schedRec.sys_id);

//Get the current date/time in correct format for duration calculation
var currentDateTime = current.requested_by_date.getGlideObject();

//Set the amount of time to add (in seconds)
var timeToAdd = 86400;
durToAdd = new GlideDuration(timeToAdd*1000);
var newDateTime = sched.add(currentDateTime, durToAdd, '');

//Set the 'requested_by_date' field to the new date/time
current.requested_by_date = newDateTime;

The post Schedule-based Date/Time Addition appeared first on ServiceNow Guru.

Security in ServiceNow is a very important, but often very confusing subject to get the hang of. ACLs, business rules, client scripts, and UI policies can all affect the security in your system to varying levels. Improper use of any of these security mechanisms can cause you some pretty serious problems so it’s important to know what you’re doing. In my experience as a ServiceNow consultant and administrator I’ve learned some things about ServiceNow security that I want all of my clients to know. I’ll explain these things in this article.

Although they are often a critical part of the overall security approach for a ServiceNow instance, this article will not address the details of security restrictions that are initiated outside of a ServiceNow system. These restrictions generally fall into the following categories…

• VPN or other network-based access restriction not configured in your ServiceNow instance
• IP Range-based Authentication Plugin
• Single Sign-on Authentication

What this article will address are the details of security restrictions within the system that affect the fields on a form or list, and rows within tables.

1 – Meet your new best friend…The Access Control List (ACL)

The Contextual Security Manager should be your FIRST AND PRIMARY line of defense when it comes to security in ServiceNow. If an element or record really needs to be secured from all angles, this is the way to do it! You need to become very familiar with how to use ACLs.

2 – Client scripts and UI policies ARE NOT security!

I feel like I should have put a few more exclamation points in on this one.:) This is such an important point to make though because it’s a very common point of confusion for people getting started with ServiceNow.

It’s very simple to set up a client script or UI policy to make a field read only. This works great when you’re looking at a form because that’s the only place where client scripts and UI policies run! What isn’t as obvious is that this “security” can easily be bypassed in a variety of ways. I’m not going to detail all of these, but I will show you the most common scenario…list editing. The following screenshots show the difference in a list between a field that has been secured by an ACL and and field that has been secured by a client script or UI policy. The client script method has no effect in any place other than a loaded form so it doesn’t secure anything in the list.

While it is possible to supplement a client script or UI policy with a ‘list_edit’ ACL, this is still a poor substitute for a truly locked-down field through the use of a full ‘write’ ACL. The bottom line here is that if it really needs to be secure, client-side methods aren’t going to do the job. Client-side methods obviously have their place, but they are designed for masking certain field inputs on a form to control the process of record creation, not permanent security of a field.

3 – Don’t use dictionary settings for security

Each dictionary entry in the system has a few fields that could potentially be used to secure fields in the system. There is a ‘Read only’ checkbox, and ‘Read roles’, ‘Write roles’, ‘Create roles’, and ‘Delete roles’ fields available. The ‘Read only’ checkbox will work, but it will interfere with any ACL security that you put in place and it’s almost guaranteed to cause serious grief for someone trying to troubleshoot a security issue with that element. The ‘roles’ fields only work with the extremely old simple security model that was used several years ago before contextual security ACLs came along. Contextual security ACLs have been the default security model for several years now. The best advice I can give here is to remove these fields from your dictionary form and don’t use them. :)

4 – Pay attention to the ‘Row-level read’ ACL exception

There is a major exception to the use of ACLs when it comes to the read operation. It’s probably best to illustrate this with a screenshot of something that you might have seen before…

What this screenshot illustrates is that ACLs securing the read operation for an entire row (TABLENAME instead of TABLENAME.FIELDNAME or TABLENAME.*) do not work well if you’re limiting access to some of the records within a table. The records aren’t visible, but you end up with a list that only shows you the records available for each page in the list (along with a count of all of the records that the user isn’t seeing) rather than a normal, compressed list of just those results that are available. ‘Row-level read’ ACLs should only be used when you want to restrict or grant access to every record in a table to a certain set of users. Any situation that only limits access to some of the records in a table requires the use of a ‘Before query’ business rule to avoid this problem.

I’ve written a couple of articles on ServiceNowGuru explaining how ‘Row-level read’ business rules work. You should read these articles for more details.
Controlling record access with ‘before query’ business rules
Fixing the ‘Before query’ business rule flaw

5 – ‘Before’ business rules and onSubmit client scripts can be used to prevent record submission

There may be specific scenarios where you want to prevent the insertion or update of a record based on something going on in that record or form. In these cases you may use a business rule or client script to accomplish your goal. Full details on this technique can be found here.

6 – Don’t forget to add ACLs for new tables you create

It’s inevitable that you’ll need to create new tables in your ServiceNow instance. It’s important to remember that ACLs for tables don’t automatically get created for you so you have to create them if you want that table to be secure. Usually it’s enough to create some simple read, write, and delete row-level ACLs but it will depend on your setup and the purpose of the particular table.

7 – Introducing or modifying any top-level (*.*) ACL can cause SERIOUS problems

Top-level ACLs impact the entire security structure of your system. It’s just usually not a good idea to modify them or introduce new ones, so leave them alone.

8 – Understand the ACL rule search order and precedence

This information is critical if you’re working with ACLs because there is a hierarchy of tables and fields and a precedence between different types of rules that needs to be considered. If you’ll educate yourself on this ordering, you’ll be able to make sense of contextual security much more quickly.

The post What Everybody Should Know About ServiceNow Security appeared first on ServiceNow Guru.

This setup allows you to track a primary CI or Business Service against a given task in the field on the form, and it also allows you to track multiple Affected CIs against a task if necessary in the related list. What I don’t like about this setup is that these are managed independently so there’s not a single place to see ALL of the Affected CIs in your environment. My solution to this problem has always been to centralize all of this information into the ‘Affected CIs’ related list by copying the ‘Configuration Item’ field value into it. This simple idea gives you a much better look into your Affected CIs for reporting, and allows for more proactive troubleshooting through CI Business Service Maps as an input into your task processes.

This customization is pretty simple and can be accomplished through the application of two business rules. The first business rule sits on the ‘Task’ table and should be configured as follows…

if(previous.cmdb_ci){
   removeCI();
}
if(current.cmdb_ci){
   addCI();
}

function removeCI(){
   //Get Affected CI records for this task and delete the CI if necessary
   var rec = new GlideRecord('task_ci');
   rec.addQuery('task', current.sys_id);
   rec.addQuery('ci_item', previous.cmdb_ci);
   rec.query();
   while(rec.next()){
      //Delete the Affected CI record
      rec.deleteRecord();
   }
}

function addCI(){
   //Get Affected CI records for this task and add the CI if necessary
   var rec = new GlideRecord('task_ci');
   rec.addQuery('task', current.sys_id);
   rec.addQuery('ci_item', current.cmdb_ci);
   rec.query();
   if(!rec.next()){
      //Create a new Affected CI record
      var ci = new GlideRecord('task_ci');
      ci.initialize();
      ci.task = current.sys_id;
      ci.ci_item = current.cmdb_ci;
      ci.insert();
   }
}

The second business rule sits on the ‘CIs Affected’ (task_ci) table and should be configured as follows…

//Check the parent task to make sure that the CI is not listed there
if(current.ci_item && current.operation() == 'delete' && current.ci_item == current.task.cmdb_ci){
   //Disallow removal
   gs.addInfoMessage('You cannot remove this CI since it is the primary CI on the associated task.</br>Please change or remove the CI from the task form.');
   current.setAbortAction(true);
}
if(current.ci_item.changes() && current.operation() == 'update' && previous.ci_item == current.task.cmdb_ci){
   //Disallow modification
   gs.addInfoMessage('You cannot change this CI since it is the primary CI on the associated task.</br>Please change or remove the CI from the task form.');
   current.setAbortAction(true);
}

Harnessing the power of the CIs Affected (task_ci) table

Once this is done, any change to the ‘Configuration Item’ field on a task record will automatically result in the creation of an ‘Affected CIs’ entry for that same CI. Now you can set up all of your reports for this area against this table. One of the most powerful things you can do with this data is to set up a BSM Map Action icon that can be displayed to show people from within a BSM map which CIs are being affected by different tasks in the system. There’s a great example on the ServiceNow Wiki that shows you how you can do this.

The post Track Affected CIs in One Place appeared first on ServiceNow Guru.