ECC Queue

Leverage ECC Queue to create an Output instruction to the MID Server to run a command to execute PowerShell

(function execute(inputs, outputs) {
/************************************************
* Get the name of a Active Mid Server
*************************************************/
var midName;
var eccObj = new GlideRecord('ecc_agent_capability_m2m');
eccObj.addEncodedQuery('capability.capabilityINPowerShell,ALL');
eccObj.addQuery('agent.status', 'Up');
eccObj.query();

if (eccObj.next()) {
    midName = 'mid.server.' + eccObj.agent.name;
}

var filePath = inputs.psname;

/* ***********************************************************************/
/* Create output ecc record to execute the powershell file in mid server */
/* ***********************************************************************/
var ecc = new GlideRecord('ecc_queue');
ecc.initialize();
ecc.agent = midName;
ecc.topic = 'Command';
ecc.name = 'powershell ' + filePath;
ecc.queue = 'output';
ecc.state = 'ready';
ecc.source = 'PowerShell';
ecc.payload = '<?xml version="1.0" encoding="UTF-8"?><parameters><parameter name="skip_sensor" value="true"/></parameters>';
ecc.insert();

})(inputs, outputs);

PowerShell

Use the PorwerShell script to upload CSV/Excel File to ServiceNow Platform via REST API.

The file is uploaded to the Attachment table with correlation to the Data Source record for the import.

##############################################
#####         FILE UPLOAD                #####
##############################################

# Eg. User name="admin", Password="admin" for this code sample.
$user = "integration"
$pass = "queretaro"

# Build auth header
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user, $pass)))

# Set proper headers
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add('Authorization',('Basic {0}' -f $base64AuthInfo))
$headers.Add('Accept','application/json')
$headers.Add('Content-Type','application/json')

# Specify endpoint uri
$uri = "https://dev99999.service-now.com/api/now/attachment/file?table_name=sys_data_source&table_sys_id=c6d53341474b01100d784168f36d436e&file_name=NewUsers.xlsx"

# Specifiy file to attach
$fileToAttach = "c:\report\NewUsers.xlsx"

# Specify HTTP method (POST, PATCH, PUT)
$method = "POST"

# Send HTTP request
$response = Invoke-WebRequest -Headers $headers -Method $method -Uri $uri -InFile $fileToAttach -ContentType 'multipart/form-data'

# Print response
$response.RawContent

Flow Designer

Create a Flow to run the import of the file to the Data Source using a schedule.

Use the custom action to run the PowerShell script passing the Path and File Name of the PS1.

(Optional) - Clean Import Set table

As optional step in the Flow, before importing the new file in the Data Source, you may want to cleanup the Import Set table and also you will want to delete the current file attached in the Data Source that is no longer required.

By Oscar Lopez

@oslovanet