Scripts
Introduction
Scripts should be stored in a text file with a .txt extension. Each command in the script file must be on a single line unless a backslash ("\") is used as a line continuation character. A "#" is used to indicate the start of a comment.
Use the following command line option to execute a script file.
-f filename
Notation
The following notation is used in the syntax specifications below.
[ created | updated ] — Optional choice. Either or neither of the keyworks may be specified.
{ output | append } — Mandatory choice. One of the keywords must be specified.
"{" clause "}" — The clause is specified between curly braces.
Note: All keywords are lower case.
DateTime Notation
Dates must be specified using the format yyyy-mm-dd or yyyy-mm-dd hh:mm:ss. If no time is specified then 00:00:00 (midnight) is assumed. All dates are in GMT.
Starting dates are inclusive. Ending dates are exclusive. The following range includes all dates in December 2012 from midnight GMT on December 1 through 12:59:59 PM GMT on December 31.
from 2012-12-01 to 2013-01-01
Two special dates are permitted. The keyword now is used to indicate the current date/time. The keywork today is used to indicated the start of the current date in GMT.
In addition, a minus or/or plus clause can be appended to a datetime. The syntax is as follows:
datetime [ minus n { days | hours | minutes | seconds } ] [ plus n { days | hours | minutes | seconds } ]
For a time span of the past 60 minutes specify
from now minus 1 hours to now
For a time span of the past week ending at 5:00 AM GMT specify
from today minus 7 days plus 5 hours to today plus 5 hours
load
The load command is used to load data from a ServiceNow table (source table) into an SQL table (destination table).
The name of the destination table is the same as the name of the source table (unless the into clause is specified). If the destination table does not exist then it will be created based on schema information retrieved from ServiceNow.
Syntax
load tablename [ into sqltablename ] [ use-keys | no-keys ] [ truncate ]
[ insert-only | update-insert | compare-timestamps ]
[ [ created | updated ] from datetime [ to datetime ] ]
[ where "{" encodedquery "}" ]
into
Used to specify an alternate name for the destination table. By default the destination table has the same name as the source table. If the destination table does not exist then it will be created based on schema information extracted from ServiceNow.
truncate
This option causes the destination table to be truncated before starting the load process.
insert-only
This load method performs inserts only. If any record already exists in the destination table then a primary key violation will be thrown. This options should only be used if it is known that the records do not exist in the destination table. This method is the default if truncate is specified.
update-insert
This is the default load method unless truncate is specified. First an attempt will be made to update the record in the destination table. If the record is not found then it will be inserted.
compare-timestamps
This load method causes the destination table to be read before an insert or update is attempted. If the record is found in the destination table then the timestamps (sys_updated_on) are compared. The destination record is updated only if the source record is more recent. If the record is not found in the destination table, it is inserted.
from and to dates
Allows specification of a record creation date range (based on sys_created_on) or update date range (based on sys_updated_on). The default is to use a creation date range. If a from date is specified then it is inclusive (not exclusive). In other words, records created on or after the from date will be included. If a to date is specified then it is exclusive (not inclusive). In other words, only records created before the to date will be included.
Dates must be specified using the format yyyy-mm-dd or yyyy-mm-dd hh:mm:ss. If no time is specified then 00:00:00 (midnight) is assumed. All dates are in GMT.
For additional details refer to DateTime Notation above.
where
Allows specification of an encoded query used to restrict the records process. The encoded query is applied in addition to any date range specified. The encoded query must appear inside curly braces.
Examples
The following command will load all Linux servers into a SQL table named linux_server. The SQL table will be created if it does not already exist. If it exists and contains data then records from ServiceNow will be inserted and/or updated based on the primary key which is sys_id.
load cmdb_ci_linux_server into linux_server
The following script will load all change_request tickets from the first half of 2012, one month at a time starting with the most recent month.
load change_request from 2012-06-01 to 2012-07-01 load change_request from 2012-05-01 to 2012-06-01 load change_request from 2012-04-01 to 2012-05-01 load change_request from 2012-03-01 to 2012-04-01 load change_request from 2012-02-01 to 2012-03-01 load change_request from 2012-01-01 to 2012-02-01
The following script will load all records from the tables sys_user, sys_user_group and sys_user_grmember. Existing records in sys_user and sys_user_group will be updated only if they have changed (based on timestamp). The table sys_user_grmember will be truncated and reloaded.
load sys_user compare-timestamps load sys_user_group compare-timestamps load sys_user_grmember truncate
The following command will load all P1 "Network" incidents updated in the last seven days through midnight GMT.
load incident into p1_network_incident from today minus 7 days to today \
where { priority<2^category=network }
refresh
This command loads records that have been updated or inserted (based on sys_updated_on) since a specified date.
Syntax
refresh tablename [ into sqltablename ]
since datetime
[ where "{" encodedquery "}" ]
Example
Note that this refresh command:
refresh change_request since today
performs exactly the same function as this load command:
load change_request updated from today to now
refresh every
This command polls the ServiceNow instance periodically looking for updated or inserted records. The command may be preceded by one or more load commands. This command will cause the application to continue running indefinitely until it is killed or cancelled.
It is also possible to have multiple refresh every commands in single script file and for them to have different frequencies. However, the application is single threaded and it will not perform multiple concurrent queries against the ServiceNow instance.
Syntax
refresh tablename [ into sqltablename ]
[ since datetime ]
every n { seconds | minutes | hours }
[ where "{" encodedquery "}" ]
into
Used to specify an alternate name for the destination table. By default the destination table has the same name as the source table. If the destination table does not exist then it will be created based on schema information extracted from ServiceNow.
every
Used to specify the frequency with which the application will poll the ServiceNow instance to check for updated and/or inserted records.
since
Used to specify a starting date/time for the initial query. The datetime may specified using the format yyyy-mm-dd hh:mm:ss or using the keyword today or now. All dates must be specified in GMT. The keyword today will be interpreted as midnight GMT.
where
An encoded query string used to restrict the records retrieved.
Examples
The following example will load all P1 incidents created or updated since 5:30 PM (GMT) yesterday and then poll the system every 5 minutes for any newly created or updated P1 incidents. The application will continue running until it is killed.
load incident from today minus 390 minutes where { priority<2 }
refresh incident every 5 minutes where { priority<2 }
The following example will load all upcoming change requests and change tasks into the the tables upcoming_change_request and upcoming_change_task respectively and then monitor the system every 10 minutes for any changes. The application will continue running until it is killed. This script will not correctly account for change requests or change tasks which are deleted after it starts running. For that you need the prune operation (described below).
load change_request into upcoming_change_request truncate \
where { start_date>javascript:gs.daysAgoEnd(0) }
load change_task into upcoming_change_task truncate \
where { request.start_date>javascript:gs.daysAgoEnd(0) }
refresh change_request into upcoming_change_request every 10 minutes \
where { start_date>javascript:gs.daysAgoEnd(0) }
refresh change_task into upcoming_change_task every 10 minutes \
where { request.start_date>javascript:gs.daysAgoEnd(0) }
prune
The prune command scans sys_audit_delete to identify records which need to be deleted in the destination table.
Syntax
prune tablename [ into sqltablename ] since datetime
Examples
prune change_task into upcoming_change_task since 2013-01-01
prune every
The prune every command polls sys_audit_delete periodically to identify records which need to be deleted in the destination table.
Syntax
prune tablename [ into sqltablename ]
[ since datetime ]
every n { seconds | minutes | hours }
Examples
prune change_task into upcoming_change_task every 60 minutes
sql
The sql command is a pass-through to the database. The application executes the command using an executeImmediate followed by a commit. The command to be executed must be enclosed in curly braces.
Syntax
sql"{" statement "}"
Examples
sql { truncate table ritm_variable }
sql { merge into ritm_variable t \
using ( \
select \
v.sys_id sc_item_option, \
ritm.sys_id sc_req_item, \
ritm.number_, \
vdef.name var_name, \
v.value var_value, \
vdef.reference ref_table, \
vdef.question_text question_text \
from \
sc_req_item ritm, \
sc_cat_item item, \
sc_item_option_mtom iv, \
sc_item_option v, \
item_option_new vdef \
where ritm.opened_at >= to_date('2012-07-01','yyyy-mm-dd') \
and item.sys_id = ritm.cat_item \
and iv.request_item = ritm.sys_id \
and v.sys_id = iv.sc_item_option \
and vdef.sys_id = v.item_option_new \
) s \
on (s.sc_item_option = t.sc_item_option) \
when matched then update \
set t.var_value = s.var_value \
when not matched then insert ( \
t.sc_item_option, t.sc_req_item, t.number_, \
t.var_name, t.var_value, t.ref_table, t.question_text) \
values ( \
s.sc_item_option, s.sc_req_item, s.number_, \
s.var_name, s.var_value, s.ref_table, s.question_text) }