Retrieve all variables (including multi-row) from any Request Item or Catalog Task - and push JSON for use in a Scripted REST API

[John Tomko]

While we prefer to initiate API calls to external systems from our ServiceNow instance when we're automating Request Item or Catalog Task execution, that is not always possible.  Sometimes our partners want to pull this data from ServiceNow.  That's not really all that hard to do using existing APIs, but if you want to pull a list of variables and their values, it gets a little messy.  If you want values from a Multi-Row Variable Set, it gets even messier.  So, we set about building a Scripted REST API our partners can use.  I've seen a few good efforts at this from the community, but nothing quite as elegant as what we wanted.  So... here's our take on this..

1. Build a Scripted REST API.  We set ours up with pretty generic settings.  We didn't need any request headers or query parameters, but we did need a resource.  Our resource is configured as follows:
2. 1. HTTP Method: GET
   2. Relative Path: /request_item/{sys_id} (we added /request_item because we'll probably end up building something similar for record producers - the script include we built is already set up to determine whether the provided sys_id is for a Request Item or a Catalog Task).
   3. Script:
      

      (function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
      
          var taskId = decodeURI(request.pathParams.sys_id);
      	var result = [];
      	var varHandler = new CHVarUtils();
      	result = varHandler.getTaskVarsById(taskId);
      	if(!result)
      		return sn_ws_err.NotFoundError("No variables found, please ensure you are passing a valid ServiceNow task record System ID (sys_id)");
      		
      	return result;
      
      })(request, response);​

3. Build a script include (the one referenced in the resource script).  This script include pulls back some helpful information about the record itself, and then gets all of the variable/value/display value sets, including for Multi Row Variable Sets:
   

   var CHVarUtils = Class.create();
   CHVarUtils.prototype = {
       initialize: function() {},
       getTaskVarsById: function(taskId) {
           
           //Given a System ID, find all variables and their internal and display values.
           var resultJson = {};
           var varArr = [];
           var mrvArr = [];
           var varCount = 0;
           var mrvCount = 0;
           var taskTable = "";
           var info = "";
   
           if (taskId) {
   
               var ts = new GlideRecord("task");
               ts.get("sys_id", taskId);
   
               if (ts.sys_id) {
                   if (ts.sys_class_name) {
                       taskTable = ts.sys_class_name;
   
                       //Get regular variables for SCTASKs and RITMs
                       if (taskTable == "sc_req_item" || taskTable == "sc_task") {
                           var item = "";
                           var itemName = "";
                           var itemRec = "";
   
                           var ci = new GlideRecord(taskTable);
                           ci.get("sys_id", taskId);
   
                           if (taskTable == "sc_req_item") {
                               item = ci.cat_item.sys_id;
                               itemRec = ci.sys_id;
                               itemName = ci.cat_item.name;
                           } else {
                               item = ci.request_item.cat_item.sys_id;
                               itemRec = ci.request_item.sys_id;
                               itemName = ci.request_item.cat_item.name;
                           }
   
                           var vars = ci.variables.getElements(true);
   
                           for (var x = 0; x < vars.length; x++) {
   
                               var v = vars[x];
   
                               if (!v.isMultiRow()) {
                                   info = v.toString();
                                   var varData = {};
                                   varData.name = v.getLabel();
                                   varData.value = v.getValue();
                                   varData.displayValue = v.getDisplayValue();
                                   varCount += 1;
                                   varArr.push(varData);
                               } else {
                                   var mrVarTable = {};
                                   mrVarTable.name = v.getLabel();                                
                                   mrvCount += 1;
                                   var rows = v.getRows();
   								mrVarTable.rowCount = rows.length;
                                   var mrRowArr = [];
   								var mrRowCollection = [];
   
                                   for (var j = 0; j < rows.length; j++) {
                                       var mrRowData = [];
                                       var cells = rows[j].getCells();
                                      for (var k = 0; k < cells.length; k++) {
                                           var mrCellData = {};
                                           mrCellData.name = cells[k].getLabel();
                                           mrCellData.value = cells[k].getCellValue();
                                           mrCellData.displayValue = cells[k].getCellDisplayValue();
                                           mrRowData.push(mrCellData);
                                      }
   									mrRowCollection.push(mrRowData);
                                   }
                                   mrVarTable.rowCollection = mrRowCollection || "(no data)";
   
                                   mrvArr.push(mrVarTable);
   
                               }
   
                           }
   
                       }
                   }
   
                   resultJson.taskId = taskId;
                   resultJson.taskNumber = ts.number;
                   resultJson.state = ts.state.getDisplayValue();
                   resultJson.stage = ts.stage.getDisplayValue();
                   resultJson.approval = ts.approval.getDisplayValue();
                   resultJson.taskType = taskTable;
                   resultJson.taskShortDescription = ts.short_description;
                   resultJson.additionalInfo = info;
                   resultJson.variableCount = varCount;
                   resultJson.multiRowVariableCount = mrvCount;
                   resultJson.variables = varArr;
                   resultJson.multiRowVariables = mrvArr;
   
                   return resultJson;
               }
           }
       },
       type: 'CHVarUtils'
   };
   
   ​

What you end up with is something like this:

{
  "result": {
    "taskId": "6dea3cdeec974462a7e751f414235ec6",
    "taskNumber": "RITM0106176",
    "state": "Open",
    "stage": null,
    "approval": "Requested",
    "taskType": "sc_req_item",
    "taskShortDescription": "Request the creation, modification, or removal of a network firewall rule or AWS Security Group rule",
    "additionalInfo": "Testing our RITM Variable API",
    "variableCount": 4,
    "multiRowVariableCount": 2,
    "variables": [
      {
        "name": "Rule Type",
        "value": "both",
        "displayValue": "Both"
      },
      {
        "name": "Application Group Impacted",
        "value": "16de082f69ab40bd95999cb6d691e2ea",
        "displayValue": "My Application"
      },
      {
        "name": "Project or Effort",
        "value": "API Testing",
        "displayValue": "API Testing"
      },
      {
        "name": "Description",
        "value": "Testing our RITM Variable API",
        "displayValue": "Testing our RITM Variable API"
      }
    ],
    "multiRowVariables": [
      {
        "name": "AWS Security Group Rules",
        "rowCount": 3,
        "rowCollection": [
          [
            {
              "name": "Action",
              "value": "add",
              "displayValue": "Add Rule"
            },
            {
              "name": "Rule Type",
              "value": "inbound",
              "displayValue": "Inbound"
            },
            {
              "name": "AWS Account",
              "value": "some_account",
              "displayValue": "Some Account"
            },
            {
              "name": "Security Group",
              "value": "sg-123456",
              "displayValue": "sg-123456"
            },
            {
              "name": "Protocol",
              "value": "tcp",
              "displayValue": "TCP"
            },
            {
              "name": "IP Address",
              "value": "100.100.100.1",
              "displayValue": "100.100.100.1"
            },
            {
              "name": "Port(s)",
              "value": "500",
              "displayValue": "500"
            },
            {
              "name": "Temporary",
              "value": "false",
              "displayValue": "false"
            },
            {
              "name": "Expiration Date",
              "value": "",
              "displayValue": ""
            }
          ],
          [
            {
              "name": "Action",
              "value": "add",
              "displayValue": "Add Rule"
            },
            {
              "name": "Rule Type",
              "value": "outbound",
              "displayValue": "Outbound"
            },
            {
              "name": "AWS Account",
              "value": "my_account",
              "displayValue": "My Account"
            },
            {
              "name": "Security Group",
              "value": "sg-blahblahblah",
              "displayValue": "sg-blahblahblah"
            },
            {
              "name": "Protocol",
              "value": "tcp",
              "displayValue": "TCP"
            },
            {
              "name": "IP Address",
              "value": "200.200.100.1",
              "displayValue": "200.200.100.1"
            },
            {
              "name": "Port(s)",
              "value": "500,600",
              "displayValue": "500,600"
            },
            {
              "name": "Temporary",
              "value": "true",
              "displayValue": "true"
            },
            {
              "name": "Expiration Date",
              "value": "2021-11-02",
              "displayValue": "2021-11-02"
            }
          ],
          [
            {
              "name": "Action",
              "value": "remove",
              "displayValue": "Remove Rule"
            },
            {
              "name": "Rule Type",
              "value": "inbound",
              "displayValue": "Inbound"
            },
            {
              "name": "AWS Account",
              "value": "R & D",
              "displayValue": "R & D"
            },
            {
              "name": "Security Group",
              "value": "sg-blahblahblah",
              "displayValue": "sg-blahblahblah"
            },
            {
              "name": "Protocol",
              "value": "tcp",
              "displayValue": "TCP"
            },
            {
              "name": "IP Address",
              "value": "200.100.100.3",
              "displayValue": "200.100.100.3"
            },
            {
              "name": "Port(s)",
              "value": "89",
              "displayValue": "89"
            },
            {
              "name": "Temporary",
              "value": "false",
              "displayValue": "false"
            },
            {
              "name": "Expiration Date",
              "value": "",
              "displayValue": ""
            }
          ]
        ]
      },
      {
        "name": "Firewall Rules",
        "rowCount": 2,
        "rowCollection": [
          [
            {
              "name": "Action",
              "value": "add",
              "displayValue": "Add Rule"
            },
            {
              "name": "Rule Type",
              "value": "permit",
              "displayValue": "Permit"
            },
            {
              "name": "Protocol",
              "value": "tcp",
              "displayValue": "TCP"
            },
            {
              "name": "Port(s)",
              "value": "100",
              "displayValue": "100"
            },
            {
              "name": "Source IP Address",
              "value": "100.100.100.1",
              "displayValue": "100.100.100.1"
            },
            {
              "name": "Destination IP Address",
              "value": "100.200.100.2",
              "displayValue": "100.200.100.2"
            },
            {
              "name": "Temporary",
              "value": "true",
              "displayValue": "true"
            },
            {
              "name": "Expiration Date",
              "value": "2021-11-03",
              "displayValue": "2021-11-03"
            }
          ],
          [
            {
              "name": "Action",
              "value": "add",
              "displayValue": "Add Rule"
            },
            {
              "name": "Rule Type",
              "value": "permit",
              "displayValue": "Permit"
            },
            {
              "name": "Protocol",
              "value": "tcp",
              "displayValue": "TCP"
            },
            {
              "name": "Port(s)",
              "value": "80, 443",
              "displayValue": "80, 443"
            },
            {
              "name": "Source IP Address",
              "value": "200.100.100.1",
              "displayValue": "200.100.100.1"
            },
            {
              "name": "Destination IP Address",
              "value": "200.200.100.2",
              "displayValue": "200.200.100.2"
            },
            {
              "name": "Temporary",
              "value": "false",
              "displayValue": "false"
            },
            {
              "name": "Expiration Date",
              "value": "",
              "displayValue": ""
            }
          ]
        ]
      }
    ]
  }
}

[Chris Swetnam]

Fantastic!

[MRC_Westat]

this was very helpful!

I added a line to also export the internal name of each variable 

for syncing the data with an external DB

varData.internal_name = v.getName();

Question: would it be possible to write a script to retrieve all of the values for just a single catalog request item given the same sys_id using all of the internal names for each field directly (where there are no multi-row variables). As an example, let's say that there were only 3 fields (internal names: user_name, url, submitted_date). This might be useful if there are variables like a user, where you might want to return their email address and location (stored in the user structure) in addition to the display name which would be their name only.

[MohammedY]

This is awesome, it helped me a lot with an automation project.