The factory analogy is apt here and is a fairly common pattern in programming.  At a high level:

• Factory: The factory represents a central machine that produces different items.
• Products: The items on the conveyor belt are the products created by the factory.
• Clients: The workers who take items off the conveyor belt are the clients using the factory.

There are a couple of key components to this technique, and we’re going to walk through them. As we work through an example, we will tie what we build to the above concepts to connect the dots!

First, let’s simplify and refine the above general example. We have two external systems we need to integrate with to create/update incidents. Each external system has its own REST API to call with a unique payload. We know which system we have to integrate with by the company associated with the user caller attribute on the incident in our system.  There are 2 companies right now, CompanyOne & CompanyTwo, that we need to integrate with.

At a high level, the steps involved in creating our factory are:

1. Create the code that would call each company’s API correctly.
2. Create a blank/dummy script include (this will be our factory).
3. Get GlideRecord reference to our factory.
4. Overwrite the script value from 3 with the string that represents one of our script includes from step 1.
5. Run the GlideRecord from 4 through GlideScriptEvaluator to get an object.

Now in more detail….

Step 1

Create script includes that call the respective API’s. As a standard we’ll refer to those script includes as CompanyOneAPI & CompanyTwoAPI. These script includes contain the unique logic needed to consume the respective company’s API. So far, so good…nothing really ‘new’, right?  Here’s the new part. In each script, the method that is called to fire things off is named the same. Something like ‘execute’. So to start the integration in each case would be:

new CompanyOneAPI().execute()

or

new CompanyTwoAPI().execute()

Step 2

Now create a ‘dummy’ script include. You can name it anything, but something descriptive is always recommended and helpful. The script include does NOT need to have any actual script in it, but you can take the default scripting given as part of creating a new script include. This script include will serve our ‘factory’. It takes some input and will give us a ‘product’. Here’s an example:

Name: Factory

Description: Just used by "factories" as a record for dynamically creating other objects.

Script:

var Factory = Class.create();
Factory.prototype = {
     /*
     This script include is used purely as a placeholder to build other objects for instantiation. It is referenced in other script includes and the script attribute is 'written' too but never saved. That 
     script is then executed to dynamically give the object that is needed based on the attributes passed in the the "factory".
     */
     initialize: function() {},
     type: 'Factory'
};

Step 3

Now that we have our “factory” script include we need to get a GlideRecord to our Factory script include:

var grFactory = newGlideRecord("sys_script_include");
grFactory.get("api_name", "Factory");

grFactory.script = "CompanyOneAPI";

• If “CompanyOneAPI” is in a specific scope, that scope would be included as well (I.e. <scope_name>.CompanyOneAPI).
• We do NOT ever save ( .update() ) the grFactory GlideRecord.

Step 5

Finally, you take the grFactory and run it through GlideScopedEvaluator to get an instantiated script, and then an object:

var evaluator = new GlideScopedEvaluator();
var model = evaluator.evaluateScript(grFactory, 'script');
var handler = new model();

Putting it all together

Why go through all the hassle above?  Take a look at step 4 above again.  We explicitly set the script to be “CompanyOneAPI”…but what if we wrapped steps 3-5 in a utility and allowed the script string we want to be instantiated by being passed in as an attribute?  Something like:

getInstance = function( script_include_name ) {
     //Get a reference to the script include created for populating it's script field with the name of the script include passed in.
     var grFactory = newGlideRecord("sys_script_include");
     grFactory.get("api_name", "x_snc_trng_util.Factory");
     grFactory.script = script_include;
     //Use GlideScopedEvaluator to turn evaluate the script above, returning an instantiated handler that is returned.
     var evaluator = newGlideScopedEvaluator();
     var model = evaluator.evaluateScript(grFactory, 'script');
     var handler = new model();
     return handler;
};

• Add an attribute to core_company to contain the script include string
• Add a look-up table that ties a reference to the company to the script include string (and possibly some other attribute for further delineation).

As we’ve gone the path of the second option for most of our implementations, we’ll look at that one at a high level:

Now we have a table we can use to look up the specific API script for a company. In our example the company would come from the caller on the incident.  We would use that in the look-up table to find the right API to use:

var LookUpGr = new GlideRecord("LookupTable");
LookUpGr.addEncodedQuery("company=<company_sys_id>^attribute=integration.incident");
LookUpGr.query();

Then, assuming we found a look-up record we could take the script string and create our handle:

if (LookUpGr.next) { 
     var data = {<JSON object with info needed for API call>}
     //Get an instantiated javascript object
     var handler = getInstance(LookUpGr.getValue('script')); 
     //Take that object and run it's execute method.  Each company API should have the same method to call (i.e. execute)
     var response = handler.execute(data); //evaluate response 
}

The last line essentially being the ‘client’ that takes the ‘product’ from the factory and uses it.

Now that we have the above framework, when we get the next company that we need to integrate with we’d need to:

1. Create CompanyThreeAPI (with an execute method) that calls the other systems API to create/update incidents.
2. Create a mapping in the look-up table for CompanyThree

Hopefully the above is a pattern you can find useful as I am sure there are many other applications where it can be applied. Please let me know what you think or if there’re any questions!

The post Implementing a Factory Pattern on ServiceNow appeared first on ServiceNow Guru.

Looking at the ServiceNow native processes, one can easily see that big portion of them are event-based, rather than synchronous. This is especially true for the processes which are not critical to the user experience or the ones which are not dependencies of other business logic.

In a nutshell, an event is logged in a queue and when system resources are available, the event is picked up and processed by the associated Script Action.

Below is a simple visual representation of the process along with explanation (source: Steven Bell) :

0. I register my new event in the Registry, create my Script Action associated to that event, and if needed my Script Include which could be called by the Script Action. Registering my event tells the Worker to listen for that event, and that it will be expected to do something with it.

1. Something executes a gs.eventQueue statement which writes an event record on the queue. BTW, this is not an exhaustive list.

2,3,4. The event worker(s), whose job it is to listen for events listed in the Registry, picks up the event and sees if there is a Script Action(s) associated with the registered event.

5,6. If a Script Action is found to run then it is executed which in turn may execute my Script Include if I choose.

Remember the info message when adding a role to a user or a group:

What’s happening behind – an event is logged to the queue and the roles are added to the group in the first possible moment, when the system has resources for that. Usually this is near real-time, but in case of higher priority operations are already queued, this will wait till they free up some processing power.

Now if one is implementing an application, based on synchronous logic, occupying almost all the system resources, this may lead to performance implications, slowing down the instance tremendously.

One possible approach in such cases is to shift from synchronous processing to event-based processing, which will lead to better performance.

But since events are being logged (unless another queue is explicitly specified) to the default queue, we might run into performance issues again.

Here comes the custom queue implementation. It is nothing more than a separate queue to which events can be queued explicitly, leveraging the fifth parameter of gs.eventQueue() API (more on that later).

Implementation

The implementation process is similar with the normal event-based logic implementation. We need to have:

• An event registered in the event registry
• A Business rule or any other logic to fire the event
• A Script action to process the event
• Custom queue processor

I will not discuss the first three, because these are pretty straightforward, and docs are easily available.

Custom queue processor implementation

The easiest way to create a processor for a custom queue is to:

• go to System Scheduler -> Scheduled Jobs -> Scheduled Jobs
• find a job with Trigger type = interval (i.e. ‘text index events process’)

• change the name (it can be anything) replace ‘text_index’ with the name of your custom queue inside the fcScriptName=javascript\:GlideEventManager(<HERE>).process(); line
• set Next action to be in the near future, i.e. 30 seconds from the current moment (This is very important in order to get the job running)

• (optional) edit the Repeat interval (short repeat interval may have some negative impact on the system performance, but at the same time, the lower the repeat interval, the sooner your event will be queued and processed)
• Right click -> Insert and stay! Do not Save/Update!

You can have one or more custom queues, depending on the purpose. These must be aligned with the system resources – nodes, semaphores, workers. I will not go deeper on these, more information can be found in the Resources chapter below.

Logging an event to a specific (custom) queue

gs.eventQuque() API accepts 5 parameters:

• Event name
• GlideRecord object
• Param1
• Param2
• (optional) queue

This fifth optional parameter ‘queue’ is the one that tells the system to which event queue an event should be logged.

We can log an event to the custom queue we have created above (‘custom_queue_one’) we can use the following line of code:

gs.eventQueue('event.name', grSomeObject,  null, null, ‘custom_queue_one’);

NB: queue name (fifth parameter) must be exactly the same as the one we’ve passed to the GlideEventManager during the process creation above.

Everything else (script actions, etc. is the same like in a normal event logging)

Good practices

• Just because you can, doesn’t mean you should – this implementation is applicable only to cases where huge amounts of records must be processed (see Performance chapter)
• Naming is important, give your names and processor readable names
• For optimal performance, multiple custom queues can be created to handle a particular event. In this case, the event logging must be done in a way that ensures even distribution between the queues. To better organize these, one possible approach can be to:
  • Create a script include, holding an array with the names of your event queues
  • Use the following line of code to randomly distribute events to the queues:

gs.eventQueue('event.name', grSomeObject,  null, null, event_queues[Math.floor(Math.random()*event_queues.length)]);

where event_queues is an array containing the names of your queues

Performance

• Even though we implement this approach to achieve performance, for low number of transactions it does not yield any performance gain, because of the Repeat interval – the longer it is, the slower will be the overall wait time
• For large number of transactions (thousands of records), the achieved performance gain can be really significant. In one of my implementations, I was able to achieve 30x faster execution.

More information

• Using Custom Queues
• Nodes and semaphores
• ServiceNow Servlet

The post Custom queue event handling in ServiceNow – Implementation steps appeared first on ServiceNow Guru.

You will use the checklist and checklist_item tables to automatically create and manage checklists for catalog tasks. The system property stores checklist configurations in a JSON format, allowing you to customize checklist items and define their mandatory status for different catalog items.

If you need to manage a large number of checklists, you can create a custom table in your scoped application to store the configurations. A custom table will help you manage the data more easily and offer better flexibility for future updates. In this case, you will need to modify the Script Include to retrieve checklist data from the new table instead of the system property.

The automation is triggered by a Business Rule in the Global Scope, which ensures that tasks cannot be closed unless all mandatory checklist items are completed. By automating this process, you can maintain consistency and reduce manual work, especially in IT service management workflows.

Key Components

System Property

The system property, catalogTask.checklists, stores the configuration for the checklists. This property uses a JSON structure to define which checklists and items are associated with specific catalog items. Each catalog item in the configuration includes the following:

• Country or Catalog: You can group the checklist by Catalog or Country at the highest level.
• Catalog Item Sys ID: Each catalog item is linked to specific service request tasks, and each task can have its own checklist.
• Checklist Name: The name of the checklist associated with the catalog item.
• Items: A list of individual checklist items, where each item includes:
  • name: The name of the checklist item.
  • order: The display order of the checklist item.
  • mandatory: A boolean that determines whether the item must be completed before the task can be closed.

If you are handling many checklists, using a custom table within your scoped application will scale better. The custom table can store the checklist data, and you can modify the Script Include to pull data from that table instead of the system property.

Checklist and Checklist Item Tables

The checklist and checklist_item tables store the checklists and their items for each catalog task. When you create a task, the Business Rule checks the system property to determine if a checklist is required. If the checklist data exists, the script inserts records into the checklist table and adds individual items to the checklist_item table.

The checklist table links the checklist to the specific catalog task using the document field. The checklist_item table stores each checklist item with details such as the name, order, and completed status. The complete field tracks whether each item has been completed.

You can also use the Checklist Template as an alternative, provided that your governance structure allows for easy management of these configurations in production environments. This approach may simplify the process, especially if you’re looking to manage checklist setups more efficiently.

Business Rule in Global Scope

The Business Rule triggers the checklist creation process when you create a new catalog task and validates the checklist completion when the task is updated. The rule queries the checklist and checklist_item tables to make sure that all mandatory items are complete before allowing the task to close.

The Business Rule needs to be in the Global Scope to use the setAbortAction(true) method, which prevents the task from closing if any mandatory checklist items are incomplete. You cannot use this method in scoped applications because they do not allow aborting database transactions directly.

System Property Configuration

Create a system property named catalogTask.checklists and store your JSON configuration defining the checklists and their items.

Example JSON:

{
  "checklists": {
    "<Country or Catalog>": {
      "sys_id_of_Item1": {
        "checklistName": {
          "items": [
            {
              "name": "Item1 Name1",
              "order": 1,
              "mandatory": true
            },
            {
              "name": "Item1 Name2",
              "order": 2,
              "mandatory": false
            },
            {
              "name": "Item1 Name3",
              "order": 3,
              "mandatory": true
            }
          ]
        }
      },
      "sys_id_of_Item2": {
        "checklistName": {
          "items": [
            {
              "name": "Item2 Name1",
              "order": 1,
              "mandatory": false
            },
            {
              "name": "Item2 Name2",
              "order": 2,
              "mandatory": true
            }
          ]
        }
      }
    }
  }
}

Script Include

The ChecklistCreator Script Include creates and manages checklists for catalog tasks. It retrieves the checklist configuration from the system property and determines whether to create a checklist for a specific catalog task.

When a checklist needs to be created, the script uses the catalog item sys_id to retrieve the relevant checklist data from the system property. If the data exists, the script automatically creates the checklist and its items for the task.

The system property defines which checklist items are mandatory. The script checks the actual items in the checklist_item table to ensure they are marked as complete. If all mandatory items are complete, the task proceeds. If not, the script blocks the task from closing and displays a message to the user.

var ChecklistCreator = Class.create();
ChecklistCreator.prototype = {
    initialize: function() {},

    /**
     * Retrieves the checklist data for a catalog item from the system property.
     * 
     * @param {String} catalogItemSysId - The sys_id of the catalog item.
     * @returns {Object|null} - Returns the checklist data as a JSON object if it exists, null otherwise.
     */
    getChecklistData: function(catalogItemSysId) {
        try {
            /* Get the JSON from the system property */
            var jsonString = gs.getProperty('x_916860_autocklst.catalogTask.checklists');
            if (!jsonString) {
                gs.error('System property x_916860_autocklst.catalogTask.checklists is not set or is empty');
                return null;
            }

            /* Parse the JSON string */
            var checklists;
            try {
                checklists = JSON.parse(jsonString);
            } catch (e) {
                gs.error('Failed to parse JSON from system property: ' + e.message);
                return null;
            }

            /* Retrieve the checklist data for the catalog item */
            for (var i = 0; i < checklists.length; i++) {
                if (checklists[i].catalog_item_sys_id === catalogItemSysId) {
                    return checklists[i];
                }
            }

            return null;
        } catch (e) {
            gs.error('Unexpected error occurred in getChecklistData: ' + e.message);
            return null;
        }
    },

    /**
     * Creates checklists and items for a catalog task based on the retrieved checklist data.
     * 
     * @param {String} catalogTaskSysId - The sys_id of the catalog task.
     * @param {Object} checklistData - The checklist data retrieved from the system property.
     */
    createChecklistsForCatalogTask: function(catalogTaskSysId, checklistData) {
        try {
            if (!checklistData) {
                gs.error('No checklist data provided for catalog task: ' + catalogTaskSysId);
                return;
            }

            var items = checklistData.items;
            if (!items) {
                gs.error('Checklist items not found for the specified catalog task and checklist name');
                return;
            }

            /* Check if a checklist already exists for the catalog task */
            var existingChecklistGR = new GlideRecord('checklist');
            existingChecklistGR.addQuery('document', catalogTaskSysId);
            existingChecklistGR.query();
            if (existingChecklistGR.next()) {
                gs.info('Checklist already exists for catalog task: ' + catalogTaskSysId);
                return;
            }

            /* Create checklist record */
            var checklistSysId;
            try {
                var checklistGR = new GlideRecord('checklist');
                checklistGR.initialize();
                checklistGR.document = catalogTaskSysId;
                checklistGR.name = checklistData.checklist_name;
                checklistGR.table = 'sc_task';
                checklistSysId = checklistGR.insert();
            } catch (e) {
                gs.error('Failed to create checklist record for catalog task: ' + e.message);
                return;
            }

            if (!checklistSysId) {
                gs.error('Failed to create checklist record for catalog task: ' + catalogTaskSysId);
                return;
            }

            /* Create checklist items */
            try {
                items.forEach(function(item) {
                    var checklistItemGR = new GlideRecord('checklist_item');
                    checklistItemGR.initialize();
                    checklistItemGR.checklist = checklistSysId;
                    checklistItemGR.name = item.name;
                    checklistItemGR.order = item.order;
                    checklistItemGR.mandatory = item.mandatory;
                    checklistItemGR.insert();
                });
            } catch (e) {
                gs.error('Failed to create checklist items: ' + e.message);
                return;
            }

            gs.info('Checklist and items created successfully for catalog task: ' + catalogTaskSysId);
        } catch (e) {
            gs.error('Unexpected error occurred in createChecklistsForCatalogTask: ' + e.message);
        }
    },

    /**
 * Checks if all mandatory checklist items are checked for a given catalog task.
 * 
 * @param {String} catalogTaskSysId - The sys_id of the catalog task.
 * @param {String} checklistSysId - The sys_id of the checklist.
 * @returns {Boolean} - Returns true if all mandatory checklist items are completed, false otherwise.
 */
areMandatoryChecklistItemsChecked: function(catalogTaskSysId, checklistSysId) {
    try {
        // Retrieve the catalog task record
        var catalogTaskGR = new GlideRecord('sc_task');
        if (!catalogTaskGR.get(catalogTaskSysId)) {
            gs.error('Catalog task record not found: ' + catalogTaskSysId);
            return false;
        }

        var ritmSysId = catalogTaskGR.getValue('request_item');  // Get the RITM sys_id
        var ritmGR = new GlideRecord('sc_req_item');
        if (!ritmGR.get(ritmSysId)) {
            gs.error('RITM record not found for catalog task: ' + catalogTaskSysId);
            return false;
        }

        var catalogItemSysId = ritmGR.getValue('cat_item');  // Get the catalog item sys_id
        
        // Get checklist data for the catalog item from the system property
        var checklistData = this.getChecklistData(catalogItemSysId);
        if (!checklistData) {
            gs.error('No checklist data found for catalog item: ' + catalogItemSysId);
            return false;
        }

        var items = checklistData.items;
        if (!items || items.length === 0) {
            gs.error('No checklist items found for catalog item: ' + catalogItemSysId);
            return false;
        }

        // Check if all mandatory checklist items are completed
        for (var i = 0; i < items.length; i++) {
            var checklistItem = items[i];
            if (checklistItem.mandatory) { // Only check mandatory items
                var checklistItemGR = new GlideRecord('checklist_item');
                checklistItemGR.addQuery('checklist', checklistSysId);
                checklistItemGR.addQuery('name', checklistItem.name);  // Match the item name from property
                checklistItemGR.query();

                if (!checklistItemGR.next() || checklistItemGR.getValue('complete') !== '1') {  // Use 'complete' field to check completion
                    gs.info('Mandatory checklist item not completed: ' + checklistItem.name);
                    return false;
                }
            }
        }

        // All mandatory items are completed
        return true;
    } catch (e) {
        gs.error('Unexpected error occurred in areMandatoryChecklistItemsChecked: ' + e.message);
        return false;
    }
},


    type: 'ChecklistCreator'
};

Business Rule

The Global Scope Business Rule runs when a catalog task is created (inserted) or updated (on state change). The ChecklistCreator Script Include is called to manage checklist creation and validation.

• On Insert: When you create a catalog task, the Business Rule retrieves the requested item (RITM) linked to the task. It uses the catalog item sys_id to get the corresponding checklist data from the system property. If the data is found and the short description of the task matches the checklist name, the checklist is created.
• On Update (When Task Is Closing): When the catalog task is updated to a closed state, the Business Rule checks whether all mandatory checklist items are complete. The associated checklist record is queried, and the ChecklistCreator Script Include validates the completion status of mandatory items. If any mandatory item is incomplete, the task closure is blocked, and an error message is shown.

/**
 * Business Rule to create and validate checklists for catalog tasks.
 * 
 * This Business Rule triggers on both insert and update operations:
 * - On insert: it creates a checklist for the catalog task if defined in the system property.
 * - On update: it validates if all mandatory checklist items are completed before closing the task.
 * 
 * @param {GlideRecord} current - The current record being processed (sc_task).
 * @param {GlideRecord} previous - The previous version of the record (null when async).
 */
(function executeRule(current, previous /*null when async*/) {
    try {
        // Instantiate the ChecklistCreator Script Include from the scoped application
        var checklistCreator = new x_916860_autocklst.ChecklistCreator();

        /**
         * Case 1: Checklist Creation on Insert
         * 
         * If a new catalog task is inserted, the script checks if a checklist needs to be created 
         * based on the catalog item and the short description of the task.
         */
        if (current.operation() == 'insert') {
            // Get the associated Requested Item (RITM) record
            var ritmGR = current.request_item.getRefRecord();
            if (ritmGR.isValidRecord()) {
                // Get the catalog item sys_id from the RITM record
                var catalogItemSysId = ritmGR.getValue('cat_item');
                // Retrieve checklist data from the system property
                var checklistData = checklistCreator.getChecklistData(catalogItemSysId);

                // If checklist data is found and the short description matches the checklist name, create the checklist
                if (checklistData && current.getValue("short_description") == checklistData.checklist_name) {
                    checklistCreator.createChecklistsForCatalogTask(current.sys_id, checklistData);
                }
            }
        }

        /**
         * Case 2: Checklist Validation on Update
         * 
         * If the catalog task is being updated to the Closed Complete state, the script checks 
         * if all mandatory checklist items are completed before allowing the task to close.
         */
        if (current.operation() == 'update' && current.state.changesTo('3')) {  // '3' represents Closed Complete state
            // Retrieve the associated checklist record for the current task
            var checklistGR = new GlideRecord('checklist');
            if (checklistGR.get('document', current.sys_id)) {
                // Validate if all mandatory checklist items are checked
                var allChecked = checklistCreator.areMandatoryChecklistItemsChecked(current.sys_id, checklistGR.sys_id);

                // If not all mandatory checklist items are checked, abort the closure and show an error message
                if (!allChecked) {
                    gs.addErrorMessage('There are mandatory checklist items that are not completed.');
                    current.setAbortAction(true);  // Prevent closure if mandatory items are not checked
                }
            }
        }

    } catch (e) {
        // Log any unexpected errors that occur during the execution of the Business Rule
        gs.error('Unexpected error in Business Rule Create and Validate Checklists: ' + e.message);
    }
})(current, previous);

Summary

By using ServiceNow’s Script Include and Business Rule, you can automate checklist creation and validation for catalog tasks, ensuring consistency and efficiency. You define checklists dynamically in a system property using a JSON format, which allows you to customize checklist items for each catalog task, including the order, mandatory status, and checklist names.

For smaller setups, the system property works well. However, for larger checklist configurations, you can create a custom table in your scoped application to store the checklist data. This makes managing a large number of checklists easier and more flexible.

With this automated process, you prevent task closures until all mandatory checklist items are completed. This reduces manual work, minimizes errors, and ensures consistent task management across your organization.

The post ServiceNow Checklist Automation: Simplifying Catalog Task Management appeared first on ServiceNow Guru.

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.

The Security Risks of Math.random() in JavaScript

Math.random() is not designed to provide cryptographic strength randomness. It is unsuitable for scenarios where strong randomness is essential for security, such as generating secure tokens, encryption keys, or passwords.

Using GlideSecureRandomUtil API in ServiceNow

ServiceNow provides a robust solution to this problem – the GlideSecureRandomUtil API. This API offers a secure method for generating random integers, long values, and strings, ensuring unpredictability and bolstering system security.

Example Use Case: Storing Active Directory Information and Generating Default Passwords

Consider a scenario where you need to store the Active Directory information of users within ServiceNow and generate default passwords for their first login. Instead of relying on the insecure Math.random(), utilizing the GlideSecureRandomUtil API ensures a higher level of security for password generation.

Here’s how you can use the GlideSecureRandomUtil API in ServiceNow to generate a secure default password for new users:

In this example, a 12-character random password is generated using the getSecureRandomString method of GlideSecureRandomUtil.

Advantages of GlideSecureRandomUtil API:

1. Cryptographically Secure: GlideSecureRandomUtil employs cryptographic algorithms to ensure randomness, mitigating the risk of predictable patterns.
2. Compliance with Security Standards: By leveraging the GlideSecureRandomUtil API, organizations can align with security best practices and compliance requirements, ensuring robust protection of sensitive data and operations.
3. Available for both Global and Scoped Applications: It offers multiple functions catering to different use cases.

Conclusion:

In a world where cybersecurity threats are a constant concern, it’s essential to prioritize the security of our applications. By understanding the limitations of Math.random() in JavaScript and embracing secure alternatives like the GlideSecureRandomUtil API in ServiceNow, we can reinforce the security of our applications and uphold best practices in random number generation.

The post The Security Risks of Math.random() and the Solution with GlideSecureRandomUtil API in ServiceNow appeared first on ServiceNow Guru.

Standard User Criteria definition form.

You can then take that UC and add/configure it on a record as having the ability to ‘can read’ or ‘cannot read’ a specific record or group of records. Probably the best known examples of this are in the OOB knowledge base and service catalog applications.

KB article can read/cannot read attributes.

Available For/Not Available For related lists on catalog items.

But what if you wanted to be able to use UCs in your custom applications? The following will provide a roadmap on how you can do this!

Lets review how UCs are used for security by using the concrete example from the knowledge base. For the Knowledge Base application there are can/cannot read attributes (KB article image shown above) on the knowledge base record as well as the individual article record.  On the knowledge base record the UCs are associated as related records and shown in the ‘Can Read’/’Cannot Read’ related lists.  On the knowledge base articles there are ‘Can Read’/’Cannot Read’ glidelist attributes directly on the record. The way the OOB security has been implemented to use those UC lists for determining if a user can read a specific knowledge article is that:

• A user can NOT be in either the knowledge base or knowledge articles cannot read UCs to be able to read the article.
• If there is a can read UC on the knowledge base, the user must be in the UC to read articles in the knowledge base.
• If there is a can read UC on the knowledge article, the user must be in the UC to read the article.
• If there are no UCs defined on either the knowledge base or article, the article is readable by any user.

This is generally the way the service catalog works as well with the catalog and catalog item.

There are other OOB applications using UCs as well….but what if you wanted to be able to use UCs in other places to control access that aren’t part of OOB applications? To do this you’d need some way of evaluating a user’s inclusion in a UC. Thankfully there is a scriptable object that does just that: UserCriteriaLoader.

UserCriteriaLoader was a utility Mike Sherman (also a long-time ServiceNow employee) and I found while trying to develop the security model for a project we were working on. It’s relatively easy to use and just needs some fairly straightforward wrappers to make it reusable for our needs.

UserCriteriaLoader has several functions on it but the main one we are going to make use of is userMatches(). The userMatches() function takes the sys_id of a user you’re checking and an array of UC sys_id’s to check against. The function then evaluates the user against those UCs and returns true/false indicating if the user is in at least one of the UCs. Something like the following:

var user = gs.getUserId();
var uc_array = [“<uc_sys_id_1>”, “<uc_sys_id_2>”, “<uc_sys_id_3>”];
var ret = sn_uc.UserCriteriaLoader.userMatches( user, uc_array);

The above is a great start as it gives you the base capability to evaluate a user’s inclusion in a set of UCs, but what we really want is a utility that wrappers the above to give us a generic way of evaluating users against UCs in the way that UCs tend to be applied to records.

The script include below gives us a reusable utility to do this in the two primary ways we found UCs to be applied to a record:

• A glidelist directly on the record
• A related list with references to the record and to UC’s

UserCriteriaLoader.userMatches() requires the user’s sys_user record sys_id and an array of UC record sys_ids. The ‘trick’ to the utility is converting either the glidelist or a list of related records into that array of UC record sys_ids. Let’s look at some specific examples of how to use each function.

//'UserCriteriaLoader.userMatches()' Sample Usage
var gr = new GlideRecord("my_custom_table"); //Table name of record for which are evaluating access
gr.get(""); //sys_id of record for which you are evaluating access
var user_can_read = new UserCriteria(gs.getUserID(), gr).evalUCGlideList("my_can_read", "my_cannot_read");

The above would return true/false depending on the user’s inclusion in the can/cannot read UCs. A couple of things to note:

• The cannot read parameter on the function is optional.  If not included it is assumed that the user is NOT part of any cannot read UCs.
• If given a cannot read parameter, the function will take it into account by determining if the given user is part of the cannot read UC(s) and if they are returning false as the ‘answer’ as being included in any can read UCs would no longer matter for functionality to be consistent with the way OOB applications us UCs.
• The above is somewhat contrived as one of the biggest use cases is where you’d already HAVE a glide record to work with as part of the platform (BRs & ACLs mainly).

Usage when the UCs are associated via a related list is a bit more complicated….but just because data model is a more complicated. The overall approach is the same in that we want to build an array of UCs for the can read and cannot read related lists and feed them to base function. 

var gr = new GlideRecord("my_custom_table");
gr.get("");
var user_can_read = new UserCriteria(gs.getUserID(), gr).evalUCRelatedList("m2mCanTable", "canGrRefFieldName", "canUCRefFieldName", "m2mCannotTable", "cannotGrRefFieldName", "cannotUCRefFieldName");

//m2mCanTable: name of the table that contains the list of can read UCs related to the record in my_custom_table.
//canGrRefFieldName: reference to a record on my_custom_table.
//canUCRefFieldName: reference to a UC record
//m2mCannotTable: name of the table that contains the list of cannot read UCs related to the record in my_custom_table.
//cannotGrRefFieldName: reference to a record on my_custom_table
//cannotUCRefFieldName: reference to a UC record

Hopefully, this is helpful and gives the ServiceNow community an extra tool in their toolbox to solve their problems with the power of the ServiceNow platform!

var UserCriteria = Class.create();
UserCriteria.prototype = {
    /**
    name: initialize
    description: Initializes the UserCriteria object by setting the user sys_id and the GlideRecord.
    param: {String} [userSysId] - sys_id of the user we want to check.
    param: {GlideRecord} [grToCheck] - GlideRecord of the record we want to check against.
    example:
    var matches = new UserCriteria(userSysID, grToCheck)
    returns: {UserCriteria} returns a initialized UserCriteria Object.
    */
    initialize: function(userSysId, grToCheck) {
        this.userSysId = userSysId;
        this.grToCheck = grToCheck;
    },

    /**
    name: evalUCGlideList
    description: Takes the field names on the table/GlideRecord that are GlideList(s) pointing too User Criteria for "Can" & "Cannot".
    param: {String} [ucCanField] - The field name of the GlideList for the "Can" User Criteria.
    param: {String} [ucCannotField] - The field name of the GlideList for the "Cannot" User Criteria.
    example:
    var matches = new UserCriteria(userSysID, grToCheck).evalUCGlideList("<can_read_uc_glidelist_field>", "<cannot_read_uc_glidelist_field>");
    returns: {boolean} Returns true if user passed in is part of any of the user criteria in the GlideList. 
    */
    evalUCGlideList: function(ucCanField, ucCannotField) {
        var ret = false;
        if (ucCannotField)
            ret = (!this._matchesUCListField(ucCannotField, false) && this._matchesUCListField(ucCanField, true));
        else
            ret = (this._matchesUCListField(ucCanField, true));

        return ret;

    },

    /**
    name: evalUCRelatedList
    description: Takes m2m tables for can and can't mappings, with ref field names of "connectors", that connects a record to User Criteria and returns true/false indicating a users access depending on related lists.
    param: {String} [m2mCanTable] - Name of the m2m table connecting the record with User Criteria 'can' access.
    param: {String} [canGrRefFieldName] - Name of the field on the m2m table that points to the GlideRecord (can access).
    param: {String} [canUCRefFieldName] - Name of the field on the m2m table that points to the User Criteria (can access).
    param: {String} [m2mCannotTable] - Name of the m2m table connecting the record with User Criteria for can't access
    param: {String} [cannotGrRefFieldName] - Name of the field on the m2m table that points to the GlideRecord (can't access).
    param: {String} [cannotUCRefFieldName] - Name of the field on the m2m table that points to the User Criteria (can't access).
    example:
    var matches = new UserCriteria(userSysID, grToCheck).evalUCRelatedList(m2mCanTable, canGrRefFieldName, canUCRefFieldName, m2mCannotTable, cannotGrRefFieldName, cannotUCRefFieldName)
    returns: {boolean} Returns true if user passed in is part of any of the user criteria in the GlideList. 
    */
    evalUCRelatedList: function(m2mCanTable, canGrRefFieldName, canUCRefFieldName, m2mCannotTable, cannotGrRefFieldName, cannotUCRefFieldName) {

        var ret = false;
        if (m2mCannotTable)
            ret = (!this._matchesUCListTable(m2mCannotTable, cannotGrRefFieldName, cannotUCRefFieldName, false) && this._matchesUCListTable(m2mCanTable, canGrRefFieldName, canUCRefFieldName, true));
        else
            ret = this._matchesUCListTable(m2mCanTable, canGrRefFieldName, canUCRefFieldName, true);
        return ret;

    },

    /**
    name: _matchesUCListField
    description: Takes the field name on the table/GlideRecord that is a GlideList pointing too User Criteria.
    param: {String} [ucField] - The field name of the GlideList for the User Criteria.
    param: {boolean} [emptyListReturn] - What to return in case the list of criteria is empty (false: for cannot read; true: for can read).

    returns: {boolean} Returns true if user passed in is part of any of the user criteria in the GlideList. 
    */
    _matchesUCListField: function(ucField, emptyListReturn) {

        var ucArray = [];
        var ucFieldValue = this.grToCheck.getValue(ucField);
        if (ucFieldValue != null)
            ucArray = ucFieldValue.split(",");

        return (ucFieldValue && !gs.isLoggedIn()) ? !emptyListReturn : this._matchesUCList(ucArray, emptyListReturn);

    },

    /**
    name: _matchesUCListTable
    description: Takes a m2m table, with ref field names of "connectors", that connects a record to User Criteria and returns true/false indicating if the user is in any of the User Criteria.
    param: {String} [m2mTable] - Name of the m2m table connecting the record with User Criteria
    param: {String} [grRefFieldName] - Name of the field on the m2m table that points to the GlideRecord.
    param: {String} [ucRefFieldName] - Name of the field on the m2m table that points to the User Criteria.
    param: {boolean} [emptyListReturn] - What to return in case the list of criteria is empty.
    
    returns: {boolean} Returns true if user passed in is part of any of the user criteria in the GlideList. 
    */
    _matchesUCListTable: function(m2mTable, grRefFieldName, ucRefFieldName, emptyListReturn) {

        //var ret = false;
        var ucArray = this._glideQuerytoArray(m2mTable, grRefFieldName, ucRefFieldName);
        return (ucArray.length > 0 && !gs.isLoggedIn()) ? !emptyListReturn : this._matchesUCList(ucArray, emptyListReturn);

    },


    /**
    name: individualUserCriteriaCheck
    description: Used to check the includance of a user in a single User Criteria. 
    param: {String} [uc] - Sys_id of a single User Criteria
    example:
    var matches = new UserCriteria(userSysID, grToCheck).individualUserCriteriaCheck("<uc_sys_id>")
    returns: {boolean} Returns true if user passed in is part of any of the user criteria. 
    */
    individualUserCriteriaCheck: function(uc) {

        var ucArrayofOne = [uc];
        return this._matchesUCList(ucArrayofOne, false);

    },

    /**
    name: _matchesUCList
    description: Makes call to OOB UserCriteriaLoader for evaluation of user/criteria and takes in to account empty user criteria.
    param: {Array} [ucArray] - Array of relevent User Criteria sys_ids.
    param: {boolean} [emptyListReturn] - what to return if User Criteria array is empty.

    returns: {boolean} boolean if user is in User Criteria passed.
    */
    _matchesUCList: function(ucArray, emptyListReturn) {

        var ret = false;
        if (this.isEmpty(ucArray))
            ret = emptyListReturn;
        else
            ret = sn_uc.UserCriteriaLoader.userMatches(this.userSysId, ucArray);
        return ret;

    },

    /**
    name: isEmpty
    description: Checks if the inpur array is empty or contains only inactive records.
    param: {Array} [ucArray] - Array of relevent User Criteria sys_ids.
    example:
    this.isEmpty(ucArray)
    returns: {boolean} True if array has no elements or if all associated records are inactive.
    */
    isEmpty: function(ucArray) {
        if (ucArray.length == 0) {
            return true;
        }
        var ucGr = new GlideRecord("user_criteria");
        ucGr.addActiveQuery();
        ucGr.addQuery("sys_id", "IN", ucArray);
        ucGr.setLimit(1);
        ucGr.query();
        return !ucGr.hasNext();
    },

    /**
    name: getAllUserCriteria
    description: Given a user sys_id returns the list of User Criteria that user is part of.
    example:
    new UserCriteria(userSysID, grToCheck).getAllUserCriteria()
    returns: {String} - List of User Criteria sys_id's that the user is part of.
    */
    getAllUserCriteria: function() {

        return sn_uc.UserCriteriaLoader.getAllUserCriteria(this.userSysId);

    },

    /**
    name: _glideQuerytoArray
    description: An internal/hidden function used to look up the related User Criteria records and build them in to an array.
    param: {String} [m2mTable] - Name of the m2m table connecting the record with User Criteria
    param: {String} [grRefFieldName] - Name of the field on the m2m table that points to the GlideRecord.
    param: {String} [ucRefFieldName] - Name of the field on the m2m table that points to the User Criteria.
    
    returns: {Array} An array of User Criteria that are related to the record via the m2m table.
    */
    _glideQuerytoArray: function(m2mTable, grRefFieldName, ucRefFieldName) {

        var ucArray = [];
        var grUC = new GlideRecord(m2mTable);
        grUC.addQuery(grRefFieldName, this.grToCheck.sys_id);
        grUC.query();
        while (grUC.next()) {
            ucArray.push(grUC.getValue(ucRefFieldName).toString());
        }

        return ucArray;
    },

    type: 'UserCriteria'
};

The post Leveraging User Criteria in your custom applications appeared first on ServiceNow Guru.

GlideSystem User Object

The GlideSystem (gs) user object is designed to be used in any server-side JavaScript (Business rules, UI Actions, System security, etc.). The following table shows how to use this object and its corresponding functions and methods.

Function/Method	Return Value	Usage
gs.getUser()	Returns a reference to the user object for the currently logged-in user.	var userObject = gs.getUser();
gs.getUserByID()	Returns a reference to the user object for the user ID (or sys_id) provided.	var userObject = gs.getUser().getUserByID('employee');
gs.getUserName()	Returns the User ID (user_name) for the currently logged-in user. e.g. 'employee'	var user_name = gs.getUserName();
gs.getUserDisplayName()	Returns the display value for the currently logged-in user. e.g. 'Joe Employee'	var userDisplay = gs.getUserDisplayName();
gs.getUserID()	Returns the sys_id string value for the currently logged-in user.	var userID = gs.getUserID();
getFirstName()	Returns the first name of the currently logged-in user.	var firstName = gs.getUser().getFirstName();
getLastName()	Returns the last name of the currently logged-in user.	var lastName = gs.getUser().getLastName();
getEmail()	Returns the email address of the currently logged-in user.	var email = gs.getUser().getEmail();
getDepartmentID()	Returns the department sys_id of the currently logged-in user.	var deptID = gs.getUser().getDepartmentID();
getCompanyID()	Returns the company sys_id of the currently logged-in user.	var companyID = gs.getUser().getCompanyID();
getCompanyRecord()	Returns the company GlideRecord of the currently logged-in user.	var company = gs.getUser().getCompanyRecord();
getLanguage()	Returns the language of the currently logged-in user.	var language = gs.getUser().getLanguage();
getLocation()	Returns the location of the currently logged-in user.	var location = gs.getUser().getLocation();
getDomainID()	Returns the domain sys_id of the currently logged-in user (only used for instances using domain separation).	var domainID = gs.getUser().getDomainID();
getDomainDisplayValue()	Returns the domain display value of the currently logged-in user (only used for instances using domain separation).	var domainName = gs.getUser().getDomainDisplayValue();
getManagerID()	Returns the manager sys_id of the currently logged-in user.	var managerID = gs.getUser().getManagerID();
getMyGroups()	Returns a list of all groups that the currently logged-in user is a member of.	var groups = gs.getUser().getMyGroups();
isMemberOf()	Returns true if the user is a member of the given group, false otherwise.	Takes either a group sys_id or a group name as an argument.
gs.hasRole()	Returns true if the user has the given role, false otherwise.	if(gs.hasRole('itil')){ //Do something... }
gs.hasRole()	Returns true if the user has one of the given roles, false otherwise.	if(gs.hasRole('itil,admin')){ //If user has 'itil' OR 'admin' role then Do something... }
hasRoles()	Returns true if the user has any roles at all, false if the user has no role (i.e. an ess user).	if(!gs.getUser().hasRoles()){ //User is an ess user... }

It is also very simple to get user information even if the attribute you want to retrieve is not listed above by using a ‘gs.getUser().getRecord()’ call as shown here…

//This script gets the user's title
gs.getUser().getRecord().getValue('title');

g_user User Object

The g_user object can be used only in UI policies and Client scripts. Contrary to its naming, it is not truly a user object. g_user is actually just a handful of cached user properties that are accessible to client-side JavaScript. This eliminates the need for most GlideRecord queries from the client to get user information (which can incur a fairly significant performance hit if not used judiciously).

Property/Method	Return value
g_user.userName	User name of the current user e.g. employee
g_user.firstName	First name of the current user e.g. Joe
g_user.lastName	Last name of the current user e.g. Employee
g_user.userID	sys_id of the current user e.g. 681ccaf9c0a8016400b98a06818d57c7
g_user.hasRole()	True if the current user has the role specified, false otherwise. ALWAYS returns true if the user has the 'admin' role. Usage: g_user.hasRole('itil')
g_user.hasRoleExactly()	True if the current user has the exact role specified, false otherwise, regardless of 'admin' role. Usage: g_user.hasRoleExactly('itil')
g_user.hasRoles()	True if the current user has at least one role specified, false otherwise. Usage: g_user.hasRoles('itil', 'admin')

It is often necessary to determine if a user is a member of a given group from the client as well. Although there is no convenience method for determining this from the client, you can get the information by performing a GlideRecord query. Here’s an example…

//Check to see if assigned to is a member of selected group
var grpName = 'YOURGROUPNAMEHERE';
var usrID = g_user.userID; //Get current user ID
var grp = new GlideRecord('sys_user_grmember');
grp.addQuery('group.name', grpName);
grp.addQuery('user', usrID);
grp.query(groupMemberCallback);

function groupMemberCallback(grp){
//If user is a member of selected group
if(grp.next()){
//Do something
alert('Is a member');
}
else{
alert('Is not a member');
}
}

To get any additional information about the currently logged-in user from a client-script or UI policy, you need to use a GlideRecord query. If at all possible, you should use a server-side technique described above since GlideRecord queries can have performance implications when initiated from a client script. For the situations where there’s no way around it, you could use a script similar to the one shown below to query from the client for more information about the currently logged in user.

//This script gets the user's title
var gr = new GlideRecord('sys_user');
gr.get(g_user.userID);
var title = gr.title;
alert(title);

Useful Scripts

There are quite a few documented examples of some common uses of these script methods. These scripts can be found on the ServiceNow docs site.

The post User Object 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.

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.