A Perl script that will export all of the data from a table in ServiceNow

export.pl

#!/usr/bin/perl

#Required Libraries
use strict;
use warnings;
use utf8;

use LWP::UserAgent;
use HTTP::Request::Common qw(GET);
use XML::XPath;

#Count records as we process them
my $recordCount = 0;

#The Main Program -- handles command line args
sub main
{
    #Print out instructions if correct num of params are not used
    if( $#ARGV < 5 ){
    	die("\nThis utility will export records from a ServiceNow table in XML format.  ".
        "A file will be created in the working directory.\n".
        "\nUSAGE: \nexport.pl <table> <baseHostURL> <username> <password> <pageSize> <showDisplayValues> [<encodedQuery>]\n".
        "\nFULL TABLE EXPORT EXAMPLE: \n".
        "export.pl problem https://myinstance.service-now.com admin Adm1n yes 100\n".
        "\nENCODED QUERY EXPORT EXAMPLE: \n".
        "export.pl problem https://myinstance.service-now.com admin Adm1n yes 100 \"state!=7^ORstate=NULL\"\n\n"
        );
	}
	
	#Apply Command Line Arguments to local variables
    my $table = $ARGV[0];
    my $baseHostUrl = $ARGV[1];
    my $username = $ARGV[2];
    my $password = $ARGV[3];
    my $pageSize = $ARGV[4];
    my $useUnloadFormat = $ARGV[5];
    my $query = "";
    if( $ARGV[6] ){
		$query = $ARGV[6];
    }
    my $outputType = "XML"; #Made this a variable in case I decide to offer other export options
    
    #Output file will use the table name as part of the resulting file
    my $outputfile = $table."_export.xml";
    
    #Unload Format controls display variables
 	my $useUnloadFormatParam = "false";
 	if($useUnloadFormat == 1 || lc($useUnloadFormat) eq "true"
 		|| lc($useUnloadFormat) eq "yes" || lc($useUnloadFormat) eq "y"){
            $useUnloadFormatParam = "true";
        }
    
    #Build up the base url for all of the requests.  Chunking parameters used later in the code
	my $url = $baseHostUrl . '/' .$table.'.do?'.$outputType.
    '&sysparm_orderby=sys_id&useUnloadFormat='.$useUnloadFormatParam.
    '&sysparm_record_count='.$pageSize;
    
    &initFile($outputfile, $outputType); #initialize output file
    
    #Recursive function that queries for the next set of records.  
    #We start this one out with a starting sys_id of 0 to ensure we are at the beginning
	&paginatedExport($url, $table, $username, $password, $outputfile, 0, $query, $useUnloadFormatParam);
	print "Exported ".$recordCount." records from the ".$table." table\n";
}

#
# Initialize an output file.  If one already exists, we overwrite it
#  @params
#	outputfile - the name of the output file
#	outputType - the type of file we will generate (eg. XML)
#
sub initFile{
    #local($a) = ($_[0]);
    
    my $outputfile = $_[0];
    my $outputType = $_[1];
	open( my $fh, ">", $outputfile ) or die "Could not create the file: ".$outputfile."\n";
	binmode($fh, ":utf8");
	if( $outputType eq "XML" ){
		say $fh '<?xml version="1.0" encoding="UTF-8"?>';
		say $fh '<xml>';
	}
	close $fh;
}

#
# Recursive function that requests a chunk of records.  Writes the data to 
# a file and then reads the last record ID and calls itself to start a new set
# starting with records that come after the last record read
#
#  @params
#	url - the base URL that doesn't change with each paged request
#	table - the name of the table we are exporting from
#	username - for authentication to ServiceNow
#	password - for the username
#	outputfile - the name of the file to write to on local filesystem
#	startID - what value to start with in this query set
#   sysParmQuery - the encoded query that is used to further filter the data
#	useUnloadFormatParam - string (true/false) whether to show display values or not
#	
sub paginatedExport{

    #Map function parameters to variables
    my $url = $_[0];
    my $table = $_[1];
    my $username = $_[2];
    my $password = $_[3];
    my $outputfile = $_[4];
    my $startID = $_[5];
    my $useUnloadFormatParam = $_[7];

    my $sysParmQuery = "";
    my $eqComparison = "";
    my $noMoreRecordsIndicator = "FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF"; #sys_id's don't go beyond this
    if($_[6]){
        #Append to existing sysparm_query string with a ^ character if one does not already exist
	    if(substr($_[6], 0, 1) eq "^"){
	    	$sysParmQuery = $_[6];
	    } else {
	    	$sysParmQuery = "^".$_[6];
	    }
	} else {
	    #when a query is used, the processor handles > and >= differently...use >= if no additional query
	    $eqComparison = "%3D";
        
	}
    
    #Get the file ready for writing.  We'll write in UTF-8 to be safe
    open( my $fh, ">>", $outputfile) or die "Could not open ".$outputfile." for appending data";
    binmode($fh, ":utf8");
    
    #Check to see if we have finished getting all records and should wrap things up
    if( $startID eq $noMoreRecordsIndicator){
    	say $fh "</xml>";
    	close $fh;
    	return;
    } else {
    	print "\nExported ".$recordCount." records from the ".$table." table so far\n";
	}
    
    #Build the URL for the request.  Should contain paging/chunking data for this iteration
    my $queryUrl = $url . 						#This part of the URL doesn't change between requests
    	"&sysparm_query=sys_id%3E" . $startID . #We add a query to start with sys_id's > than the last one we read
    	$sysParmQuery . 						#We add the optional encoded query if there is one
    	"%26sysparm_orderby=sys_id"; 			#Ordering by sys_id field since that is what we are chunking on
    print "Querying URL: " . $queryUrl . "\n";

    #Set up the HTTP Request
    my $ua = LWP::UserAgent->new();
    my $req = GET $queryUrl;
    $req->authorization_basic($username, $password);

	#Make the request
    my $content = $ua->request($req)->content;

	#Use XPATH to allow us to make a set of XML nodes that we can iterate through    
    my $xpath = XML::XPath->new(xml => $content);
    my $nodeset;
    if( $useUnloadFormatParam eq "true" ){ #Unload Format uses a different root tag
    	$nodeset = $xpath->find('/unload/'.$table);
	} else {
		$nodeset = $xpath->find('/xml/'.$table);
	}
    my $lastID = $noMoreRecordsIndicator;
    
    #Iterate through XML doc and write each record to the file
    foreach my $node ($nodeset->get_nodelist) {
    	my $nodeString = XML::XPath::XMLParser::as_string($node);
    	my $nodePath = XML::XPath->new(xml=>$nodeString);
    	$lastID = $nodePath->findvalue("/".$table."/sys_id"); #Capture latest ID
    	say $fh $nodeString."\n";
    	$recordCount ++; #Count records as we go
	}
	
	#if the last ID we read is also the same as the starting ID, then there are no more records
	if($lastID eq $startID){
		$lastID = $noMoreRecordsIndicator;
	}
	
	close $fh; #Close the file for now
	
	#Call recursively to query all records that come after this set
	paginatedExport($url, $table, $username, $password, $outputfile, $lastID, $sysParmQuery, $useUnloadFormatParam);
}

main();