Odata url in Easymorph

Hi there,
Am having troubles getting an odata feed in Easymorph.
In my browser it’s all working fine.
This is the url (adjusted it a bit for privacy reasons):
http://url.local:7068//OData/Company(‘RMS%2FCS%20LOGISTIEK’)/WSSalesOrderLines
Result: image

But in Easymorph I can’t get the same result:

These are the header details:

and this is the result:

Dmitry already mentioned this site to me, but on there are 27 headers sent. Not sure what should be in EasyMorph.
https://www.whatismybrowser.com/detect/what-http-headers-is-my-browser-sending

Any suggestions for me?

Hi Arend,

We’ve seen APIs that for some reason reject requests without a User-Agent set to one of the popular browsers. Therefore, my suggestion was to set User-Agent to the agent that corresponds to your browser. For instance:

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64)

Can you try that?

Also add:

Accept: application/xml

It seems that your server requires Negotiate (NTLM or Kerberos) authentication instead of Basic (Header WWW-Authenticate:Negotiate). You could try to use a WindowsIntegrated authentication for the WebLocation Connector.
However, WindowsIntegrated can bypass your Windows credentials, not a custom username/password.

hi, unfortunately that didn’t help to solve it.

hi, thanks for the suggestion, but the data is on a different server (connected via vpn tunnel). So using the windows auth is not the solution as they can’t be used to connect with the data.

In the browser I get a popup to enter the credentials and this works:
image

Aha, I see that @ckononenko was correct, it’s Windows authentication.

In the browser popup, do you enter your credentials or some other user’s credentials?

If that’s someone else’s credentials, can you log in to your computer as that user? If, yes, you would need to use the “Windows integrated” authentication mode in the Web Location connector, and run the workflow when logged in as that other user.

Otherwise, the only solution here would be to use PowerShell as Web Location in EasyMorph doesn’t allow specifying user credentials explicitly.

Well, to be exact, the credentials that I am entering, are the login credentials from a user account on another server, which I am connecting with from my ETL server (where EasyMorph is running).
The servers are connected through a VPN tunnel.

Do you have more information on how to use Power Shell as web location? Are there examples available?

@ArendP

See module named “PowerShell” in the attached project. It should perform a request to your OData service and return XML. Fill in parameters URL, userName, and password with your credentials.
Under the hood, it uses PowerShell command with Invoke-WebRequest script (Invoke-WebRequest (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Docs )

Let us know if something doesn’t work.

powershell web request.morph (3.4 KB)

hi, thanks for this!
I tested it and I got this output:

<?xml version="1.0" encoding="utf-8"?>Unsupported media type requested.

Execution failed.

Not sure if the User-Agent should be in the header as well?
tried to add it as follows:
Invoke-WebRequest -Credential $Credentials -Uri $url -Method 'GET' -Headers @{"accept"="application/xml","User-Agent"="Mozilla/5.0 (Windows NT 10.0; Win64; x64)"}
But that doesn’t work.

@ArendP,

Can you add also the header

"Accept-Encoding"="gzip"

try use the following line

Invoke-WebRequest -Credential $Credentials  -Uri $url -Method 'GET'  -Headers @{"Accept"="text/html,application/xhtml+xml,application/xml,application/atom+xml,application/json,application/atomsvc+xml,application/atom+xml;type=entry"} 

or check your odata service manual about the accepted content types.

This got it working, thanks a lot!

Any idea on how to filter? The following url works perfectly in my browser, but not via Powershell.

http://:7068/AMS-NAV2017-ODATA/OData/Company(‘AMS%2FDS%20LOGISTIEK’)/WSSalesInvoiceLines?$filter=Posting_Date%20ge%20datetime%272022-01-01T00:00:00%27%20and%20Posting_Date%20le%20datetime%272022-02-01T00:00:00%27

The following url does work in Powershell, so the problem is in the filtering part.
http://:7068/AMS-NAV2017-ODATA/OData/Company(‘AMS%2FDS%20LOGISTIEK’)/WSSalesInvoiceLines

Can it have something to do with the charcaters for spacing etc?
I also tried this but doesn’t work either:
http://:7068/AMS-NAV2017-ODATA/OData/Company(‘AMS%2FDS%20LOGISTIEK’)/WSSalesInvoiceLines?$filter=Posting_Date ge datetime’2022-01-01T00:00:00’ and Posting_Date le datetime’2022-02-01T00:00:00’

Hi @ArendP
try this script.
However, this approach will encode spaces in url as a plus sign. If it doesn’t work, there is an another method to encode spaces.

UPD. Trim your url, and remove the $filter part.

$url = "{url}"
$UserName = "{userName}"
$PlainPassword = "{password}"

$SecurePassword = $PlainPassword | ConvertTo-SecureString -AsPlainText -Force
$Credentials = New-Object System.Management.Automation.PSCredential `
     -ArgumentList $UserName, $SecurePassword




$Parameters = [System.Web.HttpUtility]::ParseQueryString([String]::Empty)
$Parameters['$filter'] = "Posting_Date ge datetime'2022-01-01T00:00:00' and Posting_Date le datetime'2022-02-01T00:00:00'"

$Request = [System.UriBuilder]$url
$Request.Query = $Parameters.ToString()

Invoke-WebRequest -Uri $Request.Uri -Method 'GET'   -Headers @{"Accept"="text/html,application/xhtml+xml,application/xml,application/atom+xml,application/json,application/atomsvc+xml,application/atom+xml;type=entry"} 

thanks, that works!

One remark; in the above code, you forgot to insert the credentials. So the full code is:

$url = "{url_sales-invoice-lines all}"
$UserName = "{login}"
$PlainPassword = "{password}"


$SecurePassword = $PlainPassword | ConvertTo-SecureString -AsPlainText -Force
$Credentials = New-Object System.Management.Automation.PSCredential `
     -ArgumentList $UserName, $SecurePassword


$Parameters = [System.Web.HttpUtility]::ParseQueryString([String]::Empty)
$Parameters['$filter'] = "Posting_Date ge datetime'{date90daysAgo}' and Posting_Date le datetime'{dateToday}'"

$Request = [System.UriBuilder]$url
$Request.Query = $Parameters.ToString()

Invoke-WebRequest -Credential $Credentials -Uri $Request.Uri -Method 'GET'   -Headers @{"Accept"="text/html,application/xhtml+xml,application/xml,application/atom+xml,application/json,application/atomsvc+xml,application/atom+xml;type=entry"}

thanks so much!

1 Like

Another addition to this code. If I run this on the server, I needed to include the following parameter:

-UseBasicParsing

i found it out because I got the following error message:

The response content cannot be parsed because the Internet Explorer engine is not available, or Internet Explorer’s first-launch configuration is not complete. Specify the UseBasicParsing parameter and try again.

1 Like

That’s very interesting, thank you. However, according to the documentation, it should work by default.

-UseBasicParsing
This parameter has been deprecated. Beginning with PowerShell 6.0.0, 
all Web requests use basic parsing only. 
This parameter is included for backwards compatibility only and 
any use of it has no effect on the operation of the cmdlet.

hi, yes I read it as well, but my version was older than 6.0.0…