Skip to main content
  • EN

I have a powershell script see below (that I want to convert for use with FME). I just can't seem to find a suitable way to do it. Been around FME for a while but newish to API integrations. I have an application ID and a secrete key to be used with this.

Any assistance welcome.

---------------------------------------------------------------------------------------------------------

# Get authorisation

$requestHeaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"

$requestHeaders.Add("Content-Type", "application/json")

$requestBody = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"

$requestBody.Add("ApplicationId", "xxxxxxxxxxxxxxxxxxxxxxxx")

$requestBody.Add("ApplicationSecret", "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls -bor [Net.SecurityProtocolType]::Tls11 -bor [Net.SecurityProtocolType]::Tls12

try {

   $authorisationResponse = Invoke-RestMethod 'https://xxxxxxx.xxxxxxxxxx.net/Auth/token' -Method 'POST' -Headers $requestHeaders -Body $($requestBody | ConvertTo-Json)

} catch {

   Write-Host "$(Get-Date -Format "dd/MM/yyyy HH:mm") - Authorisation failed. Update abandoned."

   break

}

Write-Host "$(Get-Date -Format "dd/MM/yyyy HH:mm") - Authorisation complete."

# Call API for full list of properties

$requestHeaders = New-Object "System.Collections.Generic.DictionaryDiString],[String]]"

$requestHeaders.Add("Authorization", "Bearer $($authorisationResponse.result.token)")

$stopwatch = osystem.diagnostics.stopwatch]::StartNew()

try {

   $fullPropertyListResponse = Invoke-RestMethod "https://xxxxx.xxxxxxxxxxxx.net/Legacy/Properties?PageSize=999999&PageNumber=1&SortKey=PropertyNumber&SortOrder=Descending&Filters=HideDefunct=true" -Method 'GET' -Headers $requestHeaders

} catch {

   Write-Host "$(Get-Date -Format "dd/MM/yyyy HH:mm") - Full property list failed. Update abandoned."

   break

}

$allProperties = $fullPropertyListResponse.result.result

$stopwatch.Stop()

Write-Host "$(Get-Date -Format "dd/MM/yyyy HH:mm") - RunTime $($stopwatch.Elapsed) - $($allProperties.count) properties returned from the API."

I have no real powershell experience but this seems like something you can mimic with the http callers in FME. You use 1 http caller to get the token, you test if the result is actually something token-like with a tester (to filter out http request errors), use a JsonExtractor to get the actual token string from the response and use that token in the next http caller to authorize yourself and download the "properties".

 

It is always fiddly to get the query exactly correct in the http caller so I always use the following workflow: A program that performs a query that I want to perform -> Postman -> FME. It is easier to troubleshoot in Postman.

Thank you for the idea. I did give it a try but I can't seem to figure it out unfortunately must be doing something wrong. Not as easy as I thought it would be.

I have now worked out the token issue and the server returns a token

{"success":true,"result":{"expiry":172799,"token":"eyJhbGciOiJIUzUxMiIsInR5cxxxxxxxxxxxxxxxxxx"}}

However I seen to be stuck on the use of the token, and the next step or transformer to use to get the data I require.

Any thoughts help appreciated

Hi @rusty​ as @birgit​ mentioned above, this will require the use of the HTTPCaller to perform a GET or POST call to the Request URL specified.

 

It looks like your PowerShell script is a two-part process where it first performs a POST call to the https://xxxxxxx.xxxxxxxxxx.net/Auth/token where it retrieves a token. This can be done with the HTTPCaller. You can then use a JSONFragmenter to extract the token from the _response_body attribute. It looks like you may have already got this step sorted which is great to hear.

 

Then you can connect this transformer to a second HTTPCaller to perform the GET call to https://xxxxx.xxxxxxxxxxxx.net/Legacy/Properties?PageSize=999999&PageNumber=1&SortKey=PropertyNumber&SortOrder=Descending&Filters=HideDefunct=true

You'll need to specify the Authorization header with the token you extracted from the _response_body of the first HTTPCaller. It might look something like this:

image 

Here are some great articles on the HTTPCaller that will help clarify the different parameters you need:

HTTP Requests with the HTTPCaller

How to Access an API using the HTTPCaller

Making a GET Request with HTTPCaller

Thank you guy's, I can now get it connected and downloading the data. I was so Close too. The bit that got me was the "Bearer @value(token)" that did the trick.

Now I have the connection and the data coming down, what it the best methodology to pull the data out of the json file. I have tried a fragmenter, and flattener but not getting the right information. It looks like the json file is a nested json file, and I am a bit stuck with this too.

Sorry to be such a pain in the butt, I haven't, done much with API json data so I'm a bit out of my depth.

 

Thanks again for the assistance it been magic. 😀

Thank you guy's, I can now get it connected and downloading the data. I was so Close too.  The bit that got me was the "Bearer @value(token)" that did the trick.

Now I have the connection and the data coming down, what it the best methodology to pull the data out of the json file. I have tried a fragmenter, and   flattener  but not getting the right information. It looks like the json file is a nested json file, and I am a bit stuck with this too. 

Sorry to be such a pain in the butt, I haven't, done much with API json data so I'm a bit out of my depth.

 

Thanks again for the assistance it been magic. 😀 

Hey rusty,

Nice to hear that it's working now. Unfortunately these things never really get easier because every API you will work with will be completely different. On your json question, you are correct with the use of the json fragmenter, however you might also need the json extractor. Unfortunately it really depends on your json structure and as long as we don't have an example I can only give you some examples.

 

Selecting an attribute : json."attributename"]

Selecting an attribute in an attribute: jsond"attributename"]w"attributename"]

Selecting an attribute which is an array/list: jsonp"attributename"]

  •  

    The last one can be used in the json fragmenter to split up your response into individual features. Take a lookt at this example json: 

    {
    	"object": "boat",
    	"items": t{
    			"name": "BestBoat",
    			"age": "25"
    		},
    		{
    			"name": "WorstBoat",
    			"age": "100"
    		},
    		{
    			"name": "NewAndGlittery",
    			"age": "1"
    		}
    	]
    }

    If I use the JSONFragmenter with the query jsonr"items"]

  •  it will create 3 features. If I have set the Fragmenter to "Flatten Query Results into Attributes" and I expose the age and name attributes I will get the following result:

    image" data-fileid="0694Q00000HYyeVQAT

    • Thanks everyone for the support it's awesome. It is now working after some sorting out of the json and the transformers, I finally got the results, using a json Fragmenter and an Attribute exposer, then an Attribute Manager.

    Many lessons learned along the way, testing and failing, until I got it.😀

    Reply


    Community Stats

    30,210
    Posts
    114,653
    Replies
    37,866
    Members
    Terms & ConditionsCookie settings
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.