How to call via Powershell a servicenow URL that exports a servicenow table to an excel file ?

[jmclej]

Hello,

So far I was using the following working script to automatically export a servicenow table :

$pathToChrome = ‘C:\Program Files\Google\Chrome\Application\chrome.exe’

$tempFolder = ‘–user-data-dir=’ + $env:Temp + ‘\temp_chrome_sc_request’

$startmode = ‘–default’$startPage = ‘https://instancelambda.service-now.com/nav_to.do?uri=%2Fsc_request_list.do%3FXLSX%26sysparm_<sysparmTypeOrField=value>%26sysparm_fields=reassignment_count,location,number,opened_at,short_description,requested_for,priority,request_state,u_business_service,business_service,calendar_duration,sla_due,made_sla,u_other_service,assignment_group,assigned_to,sys_updated_on,sys_updated_by,contact_type,closed_by,sys_created_on,sys_created_by’

$downloadpath = ‘C:\Users' + $env:USERNAME +’\Downloads'

$app=Start-Process -FilePath $pathToChrome -ArgumentList $tempFolder, $startmode, $startPage -PassThru -WindowStyle Minimized

But a GPO, that I can't modify, has just been applied which sets to YES the parameter “Ask where to save each file before downloading” in chrome (and Edge too). My script now triggers then a popup to show up, which needs manual action, and therefore I cannot use this script anymore in a Task scheduler.

After some research, I found different ways with Powershell to download a file, but I cannot make it work with this specific servicenow URL. Indeed, I don't have access to servicenow API so I need to go with the above export URL.

When I try to use Start-BitsTransfer, it creates a 0 kb-size file and when I try to use Invoke-WebRequest, I get a 33kb-size file when the file I should get is like 12Mb.
If you pay attention to my URL, it is not directly a link to a file, it is a link in servicenow that, when called, generates a file and downloads it. Servicenow uses SSO authentication method.
Do you have an idea of which additional paremeter I should use ?
I have tried adding -UseDefaultCredentials to Invoke-WebRequest but I get the same result (33kb-size file).
I have tried adding $Cred = Get-Credential and -Credential $Cred to Start-BitsTransfer but I also get the same result (0kb-size file). Get-Credential also generates a popup to enter my credential, which I don't want to do, I want to the script to use the actual credential of the user running the script.
Thanks for your help

[DrewW]

One way to go about it would be to use an export set and then create a scripted rest web service that triggers the export and then just use PowerShell to make the web call.

[jmclej]

Thanks for the answer but I don't know what an "export set" is, can you point me to some documentation about it please ? It is to be noted that on servicenow, I don't have any admin rights, can it be done without them ?

Same, when you talk about "create a scripted rest web service", this is to be done within servicenow ? If yes, I doubt I have any rights for this.

Thanks for your explanations.

[DrewW]

So you are not a ServiceNow dev you are a Powershell guy trying to export data using Powershell.  Ok that limits the options which is fine and good to know.

This is the sample script the API Explorer generates for Powershell.  Maybe that will help.  Also you will want to use a URL that is like this
https://<INSTANCE_NAME>.service-now.com/incident_list.do?sysparm_query=active%3Dtrue&sysparm_fields=...

Notice I did incident_list.do and not the api path or nav_to.do.  I also added &EXCEL to the end so the system would send it in that format.

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

# 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/xml')

# Specify endpoint uri
$uri = "https://<INSTANCE_NAME>.service-now.com/api/now/table/incident?sysparm_query=active%3Dtrue&sysparm_fields=number%2Cshort_description%2Cassignment_group&sysparm_limit=10"

# Specify HTTP method
$method = "get"

# Send HTTP request
$response = Invoke-RestMethod -Headers $headers -Method $method -Uri $uri 

# Print response
$response.RawContent

[jmclej]

I have seen this solution already, but it's using an API URL, which my company says they don't give access to it (because it's an additionnal licence cost), that 's why I am limited to the export URL.

What is the issue with using the URL I posted originally ?