-
Notifications
You must be signed in to change notification settings - Fork 7
Scripts
Scripts should be stored in a text file with a .txt extension.
Use the following command line option to execute a script file.
-f filename
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 line.
Quoted strings must be enclosed in curly braces. Quoted strings are used for encoded queries and SQL queries.
load incident where {priority<2^category=network}
sql { truncate table cmn_department }
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.
Note: All keywords are lower case.
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 keyword 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
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.
load tablename [ into sqltablename ] [ truncate ]
[ insert-only | update-insert | compare-timestamps ]
[ [ created | updated ] from datetime [ to datetime ] ]
[ where encodedquery ]
The encodedquery must be enclosed in curly braces (i.e. { } ).
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.
This option causes the destination table to be truncated before starting the load process.
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.
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.
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.
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.
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.
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}
This command loads records that have been updated or inserted (based on sys_updated_on) since a specified date.
refresh tablename [ into sqltablename ]
since datetime
[ where encodedquery ]
The encodedquery must be enclosed in curly braces (i.e. { } ).
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
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.
An encoded query string used to restrict the records retrieved.
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) }
The prune command scans sys_audit_delete to identify records which need to be deleted in the destination table.
prune tablename [ into sqltablename ] since datetime
prune change_task into upcoming_change_task since 2013-01-01
This command must appear as the first line in the script. It causes all subsequent commands in the script to be run repeatedly at the specified interval. It is typically used with a list of refresh commands. Use of every will prevent the program from terminating until it is killed.
every n { seconds | minutes | hours }
every 10 minutes
refresh incident
refresh change_request
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.
sql sqlstatement
The sqlstatement must be enclosed in curly braces (i.e. { } ). If the statement spans more than one line, you must place a "" at the end of each line except the last.
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) }