ServiceNow Writer

In order to read data from a ServiceNow instance, Striim requires a user account on the ServiceNow instance with API access and specific roles.

The OAuth plugin is active by default on new and upgraded ServiceNow instances. If your ServiceNow instance requires activation or installation, consult ServiceNow documentation. Create an OAuth API endpoint at the instance. Creating the endpoint requires the following information:

curl --location --request POST 'https://dev9679.service-now.com/oauth_token.do' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--header 'Cookie: BIGipServerpool_dev96679=2592364554.41278.0000; JSESSIONID=DD71F5EA2CB0D8D7F8921E81F05925D8; glide_session_store=F371B3362F10111005D5837CF699B660; glide_user_route=glide.ac850c71294cc9d30599c382c534f414' \
--data-urlencode 'grant_type=password' \
--data-urlencode 'client_id=fb03f7b6f101110d70314f8a47a5a9c' \
--data-urlencode 'client_secret=LmSB[O$zI' \
--data-urlencode 'username=rest.user' \
--data-urlencode 'password=Test1234'

curl --location --request POST 'https://{instance ID}.service-now.com/oauth_token.do' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--header 'Cookie: {cookie values}' \
--data-urlencode 'grant_type=refresh_token' \
--data-urlencode 'client_id={client ID}' \
--data-urlencode 'client_secret={client secret}' \
--data-urlencode 'refresh_token={refresh token value}'

Use a POST request to add data to an existing table on the ServiceNow instance. Use a PUT request to update data. Use a DELETE request to delete data. The URI for a given table is of the form {instance-id}.service-now.com/api/now/table/{table name}. Use the following command to insert data:

curl --location --request POST 'https://{instance ID}.service-now.com/api/now/table/{table name}/{unique ID}' \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--header 'Authorization: Bearer {auth token}' \
--header 'Cookie: {cookie values}' \
--data-raw '{'\''short_description'\'':'\''Unable to connect to office wifi 5'\'','\''assignment_group'\'':'\''287ebd7da9fe198100f92cc8d1d2154e'\'','\''urgency'\'':'\''2'\'','\''impact'\'':'\''2'\''}'

For data update or delete operations, add a unique ID to the URI after the table.

curl --location --request PUT 'https://{instance ID}.service-now.com/api/now/table/{table name}/{unique ID}' \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--header 'Authorization: Bearer {auth token}' \
--header 'Cookie: {cookie values}' \
--data-raw '{'\''short_description'\'':'\''Unable to connect to office wifi 8'\'','\''assignment_group'\'':'\''287ebd7da9fe198100f92cc8d1d2154e'\'','\''urgency'\'':'\''2'\'','\''impact'\'':'\''2'\''}'

ServiceNow supports hard deletes, which cannot be recovered, and soft deletes, which are recoverable. Hard deletes can be audited. By default, hard delete operations for records beginning with the sys prefix are not held for audit. Consult ServiceNow documentation for instructions on auditing hard deletes.

Soft deletes are stored in the sys_audit_delete table for up to seven days. The ServiceNow documentation has instructions on disabling audits of soft deletes and on recovering soft deleted information.

This sample TQL writes the output of a DBReader into ServiceNow.

STOP APPLICATION admin.MySQL2SNWriter;
UNDEPLOY APPLICATION admin.MySQL2SNWriter;
DROP APPLICATION admin.MySQL2SNWriter CASCADE;

CREATE OR REPLACE APPLICATION MySQL2SNWriter;

CREATE OR REPLACE SOURCE MySQL2SNWriter_src USING Global.DatabaseReader ( 
  DatabaseProviderType: 'Default', 
  FetchSize: 100, 
  QuiesceOnILCompletion: true, 
  Tables: 'waction.authors', 
  adapterName: 'DatabaseReader', 
  Password: 'w@ct10n', 
  ConnectionURL: 'jdbc:mysql://localhost:3306', 
  Username: 'root' ) 
  OUTPUT TO MySQL2SNWriter_OutputStream1;

 CREATE OR REPLACE TARGET servicenowwriter USING Global.ServiceNowWriter ( 
  ConnectionTimeOut: '60', 
  ConnectionRetries: '3', 

  ConnectionUrl: 'https://dev849543232.service-now.com',
  UserName: 'snr',
  Password: '^Pre&$EMO%6O.e_{96h+$R?rJd,=[4Vt=K)Szh?6g<J9D3,3zs8R;hpZqh]-3?C&.u-@GvSakPXH1:2eygbBDI>ou-z#GjBw[u8x', 
  ClientID: 'ce4fd5af894a11103d2c5c3a8fe075e1',  
  ClientSecret: '6Wa-cv`I7x', 
  
  BatchAPI: 'true', 
  ApplicationErrorCountThreshold: '20', 
   
  MaxConnections: '20', 
  Tables: 'waction.authors,u_authors ColumnMap(u_birthdate=birthdate,u_email=email,u_first_name=first_name,u_id=id,u_last_name=last_name)', 
  ParallelThreads: '20', 
  Mode: 'MERGE', 
  
  adapterName: 'ServiceNowWriter', 
  BatchPolicy: 'eventCount:1000, Interval:30' ) 
INPUT FROM MySQL2SNWriter_OutputStream1;


END APPLICATION MySQL2SNWriter;

This sample TQL writes the contents of a file to ServiceNow:

CREATE OR REPLACE APPLICATION KW RECOVERY 1 SECOND INTERVAL;

CREATE TYPE AccessLogType1 (
 merchantName java.lang.String,
 merchantId java.lang.String);

CREATE OR REPLACE SOURCE CSVSource USING Global.FileReader ( 
  adapterName: 'FileReader', 
  rolloverstyle: 'Default', 
  positionByEOF: false, 
  WildCard: 'posdata.csv', 
  blocksize: 64, 
  skipbom: true, 
  includesubdirectories: false, 
  directory: '/Users/vishwanath.shindhe/Documents/Project/product/Samples/Customer/PosApp/appData/' ) 
PARSE USING Global.DSVParser ( 
  trimwhitespace: false, 
  linenumber: '-1', 
  columndelimiter: ',', 
  columndelimittill: '-1', 
  trimquote: true, 
  ignoreemptycolumn: false, 
  separator: ':', 
  parserName: 'DSVParser', 
  quoteset: '\"', 
  handler: 'com.webaction.proc.DSVParser_1_0', 
  charset: 'UTF-8', 
  ignoremultiplerecordbegin: 'true', 
  ignorerowdelimiterinquote: false, 
  blockascompleterecord: false, 
  rowdelimiter: '\n', 
  nocolumndelimiter: false, 
  headerlineno: 0, 
  header: true ) 
OUTPUT TO FileStream;

CREATE STREAM TypedAccessLogStream1 OF AccessLogType1 PARTITION BY merchantId;

CREATE OR REPLACE TARGET snow USING Global.ServiceNowWriter ( 
  ClientSecret: 'RmmXEqB8GI2xGl5IfVEdiw==', 
  BatchPolicy: 'eventCount:10000, Interval:60', 
  ClientID_encrypted: 'true', 
  UserName: 'snr', 
  Password_encrypted: 'true', 
  Tables: 'u_merchant ColumnMap(u_businessName=merchantName,u_merchID=merchantId)', 
  ClientSecret_encrypted: 'true', 
  Mode: 'APPENDONLY', 
  ConnectionUrl: 'https://dev84954.service-now.com', 
  MaxConnections: '20', 
  ClientID: '3bKQkHNl8EbV6xdRLPdMCK7gkLrzmWa+Bv8ZNJ2rIy/AsM+2Gvk3dCgKfzF/QqSL', 
  Password: 'eO29sCEwzZmYPDFfOs+6JWUBYa6QGDaRLtWvm3FBP+d06UkuCjMnTQqYcTjYAo7K86p16KoJ5+LIZayJteb8QnjlbARe8rO8X5BgQrCzsX8f7w1c9gyPF4Yu/VPqTlV+/IfBA0MzPfmu7Uw9S9H4XQ==', 
  ConnectionRetries: '3', 
  ConnectionTimeOut: '60', 
  ApplicationErrorCountThreshold: '0', 
  adapterName: 'ServiceNowWriter', 
  BatchAPI: 'false' ) 
INPUT FROM TypedAccessLogStream1;

CREATE CQ AccesslogCQ1 
INSERT INTO TypedAccessLogStream1 
SELECT data[0],data[1]
FROM FileStream;

CREATE TARGET DsvWriter USING KafkaWriter VERSION '0.11.0'( 
  brokerAddress: 'localhost:9092', 
  PartitionKey: 'merchantId', 
  KafkaConfig: 'retries=3;retry.backoff.ms=500', 
  Mode: 'Sync', 
  Topic: 'dsve1ptest' ) 
FORMAT USING DSVFormatter  ( 
 ) 
INPUT FROM TypedAccessLogStream1;

CREATE TARGET sys USING Global.SysOut ( 
  name: 'ss' ) 
INPUT FROM TypedAccessLogStream1;

END APPLICATION KW;