Retrieving Attachments from ServiceNow to Local Server using powershell

[Naveen Velagapu]

PowerShell script to fetch the attachments from ServiceNow to local server. Though exception handling is not implemented yet , this will do the job. Internally this script uses "Aggregate API , Table API , Attachment API" to dump the files.

Pre execution steps - Update below variables with relevant details

• $userName : with User id of ServiceNow user record
• $password  : with User password
• $logFilePath : with a log file path , all the execution log will be stored in it
• $instanceUrl : with ServiceNow Instance to connect
• $baseFolderPath: with initial folder where we need to create subfolder , by default that folder should exist
• $tables : with list of table names for attachments need to be retrieved
• $relativeTablePath: with table and its relevant subdirectory names, script will automatically create if folder doesnt exist
• $tableFilters: with encoded queries to be applied on tables

Note:

• Exception handling is not yet implemented
• In some instances , huge files download might be incomplete, In such cases retrigger script

$userName = "XXXXXXXX"; #service user id
$password = "XXXXXXX"; #servicenow user password
$AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $userName, $password)));
$method ="get"; #http method

$logFilePath = 'XXXXXXXXXXXXX'; #stores the log generated by script

$instanceUrl = "https://XXXXXXX.service-now.com"; #instance url 
$stepCount =20; #step count to be used for pagination 

$baseFolderPath ="XXXXXXXXXX"; #base folder path , all sub folder will be created under this path


$tables = @('incident','change_request'); #list of tables for which attachments need to be dumped

#directory names to be created in base folder
$relativeTablePath = @{
	incident = "Incident"
	change_request = "Change Requests"
}
#encoded query to be applied when retrieving data from ServiceNow using table API
$tableFilters =@{
incident = "number=INC0010071"
change_request = "number=CHG0030151"		
}

function Get-NewFileName{

    param(
    [string]$suffix,
    [string]$fileName
    )
     $timeStamp =Get-Date -Format s | ForEach-Object { $_ -replace ":", "." } ;

    $newFileName = $suffix+"_"+$timeStamp+"_"+$fileName; 

    if($newFileName.Length -lt 255){
    return $newFileName ;
    }else{
    
      $newFileName =   $suffix+"_"+$fileName;
        
      if($newFileName.Length -lt 255) {
      return $newFileName;
      }else{
      return $fileName;

      }


    }


}


function Get-Dircreated{

param(
$folderPath
)

if( !(Test-Path $folderPath) ){

New-Item $folderPath -ItemType "directory"

}


}


function Logger{
    
    param(
    [string] $message
    )
    $timestamp = Get-Date -Format s;
    $message =  $timestamp +' >> ' +  $message;
    
    Add-content $logFilePath -value $message;

}



Write-Host "------- Started Processing , Check  "+ $logFilePath +" for execution details.. ---------" ;

Logger -message ("################  STARTED ##################");

ForEach($table in $tables){
	
Logger -message ("Processing data for table: " + $table); 

$aggHeaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$aggHeaders.Add('Authorization',('Basic {0}' -f $AuthInfo))
$aggHeaders.Add('Accept','application/json');
$aggHeaders.Add('Content-Type','application/json');

$aggBody = @{
sysparm_count=$true
}

#append encoded query if exists
if($tableFilters[$table] -and ($tableFilters[$table] -ne $null)){
$aggBody.sysparm_query = $tableFilters[$table]	
}

$aggUrl = $instanceUrl + "/api/now/stats/" + $table;



#calls aggregate API and fetches total record count
$aggResponse = Invoke-RestMethod  -Headers $aggHeaders -Method $method -Uri $aggUrl -Body $aggBody;

$totalRecordCount = $aggResponse.result.stats.count;

Logger -message ("Total Records found in table :: " + $table + " :: " +$totalRecordCount);


for(($step =0) ; $step -lt $totalRecordCount ; $step = $step+$stepCount){

Logger -message ("Fetching records from table :: "+ $table + " offset :: "+ $step + " to " +($step+$stepCount));

#handles record pagination
$recordHeaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$recordHeaders.Add('Authorization',('Basic {0}' -f $AuthInfo))
$recordHeaders.Add('Accept','application/json');
$recordHeaders.Add('Content-Type','application/json');

$recordUrl = $instanceUrl + "/api/now/table/" + $table;

$recordBody = @{
 sysparm_fields = "number,sys_id"
 sysparm_limit = $stepCount
 sysparm_offset = $step
}

#append encoded query if exists
if($tableFilters[$table] -and ($tableFilters[$table] -ne $null)){
$recordBody.sysparm_query = $tableFilters[$table]	
}


#get records using table api 

$recordResponses = Invoke-RestMethod  -Headers $recordHeaders -Method $method -Uri $recordUrl -Body $recordBody;

forEach($recordResp in $recordResponses.result){
	
	#fetch attachment meta data for each record
	
$attachMetaHeaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$attachMetaHeaders.Add('Authorization',('Basic {0}' -f $AuthInfo))
$attachMetaHeaders.Add('Accept','application/json');
$attachMetaHeaders.Add('Content-Type','application/json');
		
$attachMetaBody = @{
 sysparm_query = "table_name="+$table+"^table_sys_id="+$recordResp.sys_id
};

$attachmentMetaUrl = $instanceUrl + "/api/now/attachment" ;

Logger -message ("Fetching Attachment meta data for record :: "+ $recordResp.number +"  sys_id :: "+ $recordResp.sys_id);

$attachMetaResponses = Invoke-RestMethod  -Headers $attachMetaHeaders -Method $method -Uri $attachmentMetaUrl -Body $attachMetaBody;

forEach($attachMetaResp in $attachMetaResponses.result){
	
$attachContentHeaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$attachContentHeaders.Add('Authorization',('Basic {0}' -f $AuthInfo))
$attachContentHeaders.Add('Accept','*/*');
$attachContentHeaders.Add('Content-Type','application/json');
	
$attachContentUrl = $instanceUrl + "/api/now/attachment/" +$attachMetaResp.sys_id +"/file";	

Logger -message ("Started downloading file :: "+ $attachMetaResp.file_name +"  from endpoint :: "+ $attachContentUrl);

$folderPath = Join-Path $baseFolderPath  $relativeTablePath[$table];

Get-Dircreated -folderPath $folderPath

$newFileName = Get-NewFileName -suffix $recordResp.number -fileName $attachMetaResp.file_name;
$filePath = Join-Path $folderPath $newFileName;

Invoke-RestMethod -Uri $attachContentUrl -Method $method -Headers $attachContentHeaders -OutFile $filePath ;

Logger -message ("File Downloaded successfully , New file name :: "+$newFileName  + " Path to file :: "+$filePath);

}	
}	
}
}
Logger -message ("################  COMPLETED ###################");
Write-Host "------- Processing Complete ---------" ;

Thanks !!

[Adam Stout]

Can you explain the use cases when you would want to use this approach instead of using the ServiceNow CLI?  In the past, I can understand having PowerShell connect directly through web services, but is that still needed today?

[Naveen Velagapu]

Hey Adam, 

The intention of this is to download attachments and rename them in way that external applications can consume. Primarily intended to be executed by app teams in their local servers for consumption where additional installations can't be done. However this is the implementation that i have done in past and shared as it might be useful for anyone with similar requirement.

I knew we can consume OOB / scripted API as commands using CLI, Never explored that option probably worth reviewing. 

[RacheleTashjian]

I'm currently on the Yokohama version and am looking for a way to copy a .jpg file out of an incident ticket and copy it out to our sysvol and rename the file (the file is the lock screen for all our devices).  Would this script work for that?