ServiceNow: SOAP Journal

Integration using Web Services

Perl Queries 101

This post is about using Perl to query data from ServiceNow.  Let us look at several solutions to a simple problem:  print a list of all the operational servers in your CMDB.

Solution 1

Here is our first solution. The getKeys method is called to obtain a list of sys_ids.  The split function converts the result from a string of comma separated values into an array.  A loop retrieves the records one at a time.  The code is nice and tight.  If you know SOAP::Lite, it may even make sense to you.

use SOAP::Lite;

sub SOAP::Transport::HTTP::Client::get_basic_credentials {
    return $username => $password;
}

my $soap = SOAP::Lite->
    proxy("https://myinstance.service-now.com/cmdb_ci_server.do?SOAP");
my $method = SOAP::Data->name("getKeys")->
    attr({xmlns => "http://www.service-now.com/"});
my @params = (SOAP::Data->name("operational_status" => "1"));
my $result = $soap->call($method => @params)->result;
my @serverKeys = split(",", $result);

foreach my $sys_id (@serverKeys) {
    my $method = SOAP::Data->name("get")->
        attr({xmlns => "http://www.service-now.com/"});
    my @params = (SOAP::Data->name("sys_id" => $sys_id));
    my $serverRec = $soap->call($method => @params)->body->{"getResponse"};
    print $serverRec->{name}, "\n";
}

The problem with Solution 1 is that it is very slow.  It performs a separate Web Service call for each server.

Solution 2

Solution 2 is much faster.  It uses a single getRecords call to retrieve all the servers.

use SOAP::Lite;

sub SOAP::Transport::HTTP::Client::get_basic_credentials {
    return $username => $password;
}

my $soap = SOAP::Lite->
    proxy("https://myinstance.service-now.com/cmdb_ci_server.do?SOAP");

my $method = SOAP::Data->name("getRecords")->
    attr({xmlns => "http://www.service-now.com/"});
my @params = (SOAP::Data->name("operational_status" => "1"));
my $som = $soap->call($method => @params);
my @serverData = @{$som->body->{getRecordsResponse}->{getRecordsResult}};

foreach my $serverRec (@serverData) {
    print $serverRec->{name}, "\n";
}

The problem with Solution 2 is that it only returns the first 250 servers.  For performance reasons, ServiceNow limits the number of records that can be returned in a single Web Services call.  If you have 1000  servers, you can read them all by adding __limit as an extended query parameter.  If you have 100,000 servers, there is no alternative other than to fetch the data in chunks.

Solution 3

The next solution uses the ServiceNow Perl API.

use ServiceNow;
use ServiceNow::Configuration;

my $config = ServiceNow::Configuration->new();

$config->setSoapEndPoint("https://myinstance.service-now.com/");
$config->setUserName($username);
$config->setUserPassword($password);

my $server = ServiceNow::GlideRecord->new($config, "cmdb_ci_server");
$server->addQuery("operational_status", "1");
$server->query();

while ($server->next()) {
    print $server->getValue("name"), "\n";
}

ServiceNow’s perl API is designed to mimic their JavaScript API. This code is much easier to read. But surprisingly, this API suffers from the same limitation as the previous solution. It only returns the first 250 records, and it gives no indication that there is more data which was not returned.

Solution 4

The last two examples use the ServiceNow::SOAP module, and adhere to ServiceNow’s best practice recommendation for retrieving a large number of records, which is to use the getKeys method from Solution 1 to obtain a list of sys_ids and then fetch the records in chunks using an __encoded_query of the form sys_idINvalue,value,value….

This is how ServiceNow::SOAP works:  The query method calls getKeys.  The fetch method calls getRecords to retrieve the next chunk of 250.

use ServiceNow::SOAP;

my $sn = ServiceNow("myinstance", $username, $password);
my $table = $sn->table("cmdb_ci_server");
my $query = $table->query("operational_status=1");
while (my @serverData = $query->fetch()) {
    foreach my $serverRec (@serverData) {
        print $serverRec->{name}, "\n";
    }
}

Solution 5

The final example uses the fetchAll method, which simply internalizes the fetch loop.

use ServiceNow::SOAP;

my $sn = ServiceNow("myinstance", $username, $password);
my $table = $sn->table("cmdb_ci_server");
my @serverData = $table->query("operational_status=1")->fetchAll();

foreach my $serverRec (@serverData) {
    print $serverRec->{name}, "\n";
}

Note

This post of May 2013 was updated in May 2015 to use ServiceNow::SOAP instead of AltServiceNow which has been abandoned.

The example of using __first_row and __last_row extended query parameters has been removed from this post. This approach is not recommended by ServiceNow for large tables, primarily because it suffers from performance degradation as the value of __first_row increases.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: