ServiceNow: SOAP Journal

Integration using Web Services

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";

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: