ServiceNow: SOAP Journal

Integration using Web Services

Monthly Archives: May 2015

Using map for efficient Perl queries

Suppose we have a range of dates, and we want to write a Perl script that finds all incident tickets within that range, and prints the incident number, the name of the assignment group and the assignment group manager. To write a ServiceNow report that generates the data as a CSV file is extremely simple. Grabbing the data efficiently using the Direct Web Services API is a bit trickier.

To translate the Assignment Group sys_id into a name, we can take advantage of the display value feature. However, the same technique does not work for the Manager name because it is a double hop from the incident record. We could create a ServiceNow view to pull the necessary columns; but let’s assume we want to do it only with Web Services.

The following example shows how we can use Perl’s map function to efficiently retrieve the necessary sys_user_group data into a Perl hash for quick lookups. First, on line 13, we use the ServiceNow::SOAP query and fetchAll methods to retrieve the pertinent incident records.  Then, on line 14, we use map to extract a list of group sys_ids. We will only pull from ServiceNow those groups that are relevant to our query. We use the List::MoreUtils uniq function to discard any duplicates. On the next line we use asQuery and fetchAll to retrieve all the related sys_user_group records in a singe Web Services call.  Then, on line 16, we use map again: this time to convert the list of sys_user_group records in a hash that is keyed by the group sys_id.  As we loop through our incident records, we can look up any group in our hash (line 23) to pull the associated details.

use strict;
use List::MoreUtils qw(uniq);
use ServiceNow::SOAP;
my $instance = "myinstance";
my $user = "soap.perl";
my $pass = "password";
my $sn = ServiceNow($instance, $user, $pass, dv=>'all', trace=>1);

my $start_date = "2015-01-01";
my $end_date = "2015-04-30";
my $filter = "opened_at>=$start_date^opened_at<$end_date";

my @inc_recs = $sn->table("incident")->query($filter)->fetchAll();
my @grp_keys = uniq map { $_->{assignment_group} } @inc_recs;
my @grp_recs = $sn->table("sys_user_group")->asQuery(@grp_keys)->fetchAll();
my %grp_hash = map { $_->{sys_id} => $_ } @grp_recs;

foreach my $inc_rec (@inc_recs) {
    my $number = $inc_rec->{number};
    my $opened = $inc_rec->{opened_at};
    my $grp_id = $inc_rec->{assignment_group};
    my $grp_name = $inc_rec->{dv_assignment_group};
    my $grp_rec = $grp_hash{$grp_id};
    my $mgr_name = $grp_rec ? $grp_rec->{dv_manager} : "";
    print "$number | $opened | $grp_name | $mgr_name\n";

Announcing ServiceNow::SOAP – a better Perl API

I am excited to announce the release on CPAN of ServiceNow::SOAP — a better Perl API for ServiceNow.

This module has the following features:

  • Support for both Direct and Scripted Web Services.
  • The Direct Web Services methods closely match ServiceNow’s published methods, allowing you to take advantage of all features documented in the ServiceNow wiki.
  • Easy to use methods for querying large tables.  These methods follow ServiceNow’s best practice recommendation, which is to call getKeys before calling getRecords.
  • Query performance can be improved by limiting the list of columns returned.
  • Support for insertMultiple.
  • Detailed POD documentation, with lots of examples.

If you are using Perl, please check out ServiceNow::SOAP here, and give me your feedback.