Quick Preface: Many of my friends and community members have written their own custom processors, or tools to give them powerful export capabilities from ServiceNow. Crossfuze’s Scheduled Data Extract, Jason Petty’s FTP Export tool, and others come to mind. However, the purpose of this article is to focus on using the Out-of-Box export interface that uses no other technology other than the HTTP Requestor (a Perl script, in this case).
The Basics of Chunking an Export
ServiceNow offers a number of out-of-box ways to export its data. However, other than the ODBC driver, all of the export mechanisms have record limits placed on them so as not to bring down the instance when exporting a large number of records from a table.
The XML Web Service, however, does have some capabilities that allow you to easily and reliably chunk the requests using paged queries. Using the wiki documentation on exporting large amount of data, you can get the general feel for how to leverage the XML web service processor to export the full contents of a table in a paged manner so as to use several smaller requests that don’t bring the instance to its knees.
Essentially, through a series of request parameters, you sort the data on a field in the table. In most cases this would be the sys_id field. You also give it a max number of records to return in a given request. Finally, you specify a beginning value to start with in each given request (eg. use the last value of the sorted field from the previous request made). Optionally, you can choose to have the data exported in “unload” format that shows both Display Values as well as sys_id values. You can also filter the data before exporting it so that you only bring in data that matches certain criteria.
Any REST based client should be able to export data in this manner as long as it can read the response and generate a new request based on the last record that was brought over.
In order to show you how this is done, I have created a Perl Script/Utility that allows you to quickly and easily export data from a ServiceNow table into a file on your file system.
Perl-based Utility
The perl utility does require that you have Perl installed with some of the core libraries. Additionally, you will need the following Perl libraries that are available via CPAN:
LWP::UserAgent
HTTP::Request
XML::XPath
The script will accept the following command line arguements:
Table – The name of the ServiceNow table. (Not the table label, but the actual name)
Base Instance URL – This is the full URL to access the ServiceNow instance (eg. https://MyInstance.service-now.com)
Username – A username for a user that has privleges to access the table and its data
Password – The password for the user
Page Size – How many records should be returned with each web request. There is no hard and fast rule for this, however, I like to keep it around 100 or 1000. Test to see what works best for you.
Show Display Values – YES: show the display values for records in addition to the sys_id; NO – return only sys_id’s for reference field data
Encoded Query (optional) – An encoded query that filters the data in case you only want to export qualified data.
Running the script as simply:
export.pl
will give you all of the parameters as well as some examples.
Here is the code. I tried to make a lot of comments so as to teach you what is going on under the covers:
John is the Co-Founder of Yansa Labs (www.YansaLabs.com). John founded Yansa Labs as a company dedicated to building innovative solutions on the ServiceNow platform. He is a major contributor to the ServiceNow ecosystem. John served as the platform and integration architect at the company for several years.
Excellent solution. I am not versed with Perl. How do we exactly use this script to trigger the exports?
John Andersen
on October 9, 2013 at 7:05 am
@Ravish – Perl can be called on the command line, either manually, by other program, in a cronjob, or via a ServiceNow MID Server.
Daniel Dräs
on January 24, 2014 at 4:30 am
Hi John, will the data being exporting by this script be importable again? Say could that we used to migrate from one instance to another without cloning the whole system?
Thanks!
John Andersen
on February 3, 2014 at 10:42 am
@Daniel, you would have to set up a Web Service import set and pull that file into the other instance. If you are looking at cloning some data to push to another instance, you *may* want to check out the following link: ServiceNow Data Export Utility. Just keep in mind that you should use caution with that utility and test the import in a non-production instance. This is a system-style import/export mechanism which may bypass some business rules, process layers, etc. It is more of a straight clone of data.
Prateek Sharma
on April 29, 2014 at 12:28 am
John,
Please let me know is there a way to export SN data(large amount of data) to third party on schedule basis?
Shakti
on April 30, 2014 at 8:34 am
Hi John,
I have a question in relation to exporting data into XML but not via Perl.
I am trying to build an interface between service now and a third party application and the interface needs to be via xml.
The interface is to communicate using XML files attached to emails to enact the following functionality;
Call Logging
• Send New Call information to Third part system from a service now
• Send New Call Logged information to a service now from third party system
• Send New Call Logged Error information to service now from third party system
When a service call is successfully recorded on a service now, an XML file will be produced, attached to an email and sent to a specified address of third party system. The information held within the XML document will allow the third party system to create a corresponding call. An XML document will be produced by the third party system, attached to an email and sent to a specified email address of service now system.
Do you think this is possible, if so what would be the best approach (appreciate if you can share some pointers to code since I am very new to service now scripting)
John Andersen
on May 6, 2014 at 8:49 am
@Prateek, There are several ways. You can use this tool, the ServiceNow ODBC Driver, or CrossFuze’s Scheduled Export tool, for example.
Antony Mirza
on December 3, 2014 at 2:21 pm
Hi John, We are exporting CMDB data in xml format using SOAP via ECC queue. Due to high amount of data export is not getting complete.
Can you please suggest something to achieve the same using SOAP in batches?
Brgds, AM
manasa
on November 17, 2016 at 7:34 am
Hi,
Is it that your Solution works only if there is existing table in service now for pulling 10k records?. What if we created ui page which displays 10k records but because its response time is huge can we export data via Excel without displaying to user.If so how? Any ideas related to pagination.
Excellent solution. I am not versed with Perl. How do we exactly use this script to trigger the exports?
@Ravish – Perl can be called on the command line, either manually, by other program, in a cronjob, or via a ServiceNow MID Server.
Hi John, will the data being exporting by this script be importable again? Say could that we used to migrate from one instance to another without cloning the whole system?
Thanks!
@Daniel, you would have to set up a Web Service import set and pull that file into the other instance. If you are looking at cloning some data to push to another instance, you *may* want to check out the following link: ServiceNow Data Export Utility. Just keep in mind that you should use caution with that utility and test the import in a non-production instance. This is a system-style import/export mechanism which may bypass some business rules, process layers, etc. It is more of a straight clone of data.
John,
Please let me know is there a way to export SN data(large amount of data) to third party on schedule basis?
Hi John,
I have a question in relation to exporting data into XML but not via Perl.
I am trying to build an interface between service now and a third party application and the interface needs to be via xml.
The interface is to communicate using XML files attached to emails to enact the following functionality;
Call Logging
• Send New Call information to Third part system from a service now
• Send New Call Logged information to a service now from third party system
• Send New Call Logged Error information to service now from third party system
When a service call is successfully recorded on a service now, an XML file will be produced, attached to an email and sent to a specified address of third party system. The information held within the XML document will allow the third party system to create a corresponding call. An XML document will be produced by the third party system, attached to an email and sent to a specified email address of service now system.
Do you think this is possible, if so what would be the best approach (appreciate if you can share some pointers to code since I am very new to service now scripting)
@Prateek, There are several ways. You can use this tool, the ServiceNow ODBC Driver, or CrossFuze’s Scheduled Export tool, for example.
Hi John, We are exporting CMDB data in xml format using SOAP via ECC queue. Due to high amount of data export is not getting complete.
Can you please suggest something to achieve the same using SOAP in batches?
Brgds, AM
Hi,
Is it that your Solution works only if there is existing table in service now for pulling 10k records?. What if we created ui page which displays 10k records but because its response time is huge can we export data via Excel without displaying to user.If so how? Any ideas related to pagination.