Help wanted - how to read data from webservice and write it to Azure SQL

PowerShell Help
1

Hi,

I have access to a Point of Sales web service from where I can read sales from the registers. I would like to create an automated task in Azure with a PS script that once a day could read the sales and import the numbers to a SQL Azure database.

The source string looks like this (username and password changed): https://login.onlinepos.dk/api/external/?agreementnumber=0123&username=fe3135664ce62ee517caea709ab214ab&password=c0020b0f998c87184b&function=Sales_Export&from=1420412401&to=1424193458

The timestamps needs to be automatically calculated and in EPOCH format when reading data e.g. the last 24 hours each time the runbook run.

When data is read they will have to be imported in to an existing SQL database in a specific view hosted in Azure.

I am a novice into PowerShell but have been told it can do the trick.

Any help will be highly appreciated.

Thanks in advance,

Kind regards
Morten

2

Well… what, specifically, do you need help with? Right now you have a pretty high level problem statement, but short of just writing a script for you, I’m not sure what to help with.

3

Here is a short snippet on how to create the “from” and “to” timestamps in EPOCH format (Unix timestamp).

$now = [DateTime]::UtcNow
$epochBase = [DateTime]'1970-01-01 00:00:00'

$fromEpoch = [UInt32]($now.AddHours(-24) - $epochBase).TotalSeconds
$toEpoch = [UInt32]($now - $epochBase).TotalSeconds
  
$fromEpoch
$toEpoch

To invoke tha actual request reading data from the web service I would attempt to use the PowerShell cmdlet Invoke-RestMethod cmdlet. As alternative Invoke-WebRequest if the API doesn’t return a JSON document.

  1. Check out the examples of the Invoke-RestMethod and Invoke-WebRequest documentation pages:
    https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.utility/invoke-restmethod
    https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.utility/invoke-webrequest

  2. Use your Bing/Google fue to look for examples related to Invoke-RestMethod.

  3. Have a look at the code of this PowerShell module for the OpenWeather API from a fellow PowerSheller:
    PoshWeather/PoshWeather.psm1 at master · willcodeforpizza/PoshWeather · GitHub

I hope above is helpful to get you going further. Please let us know any further questions. If you code already, please feel free to post it in “pre” tags or reference a GitHub Gist.

4

Hi Daniel,

Thank you for your help. I must admit this will be too complicated for me and I will need some assistance for making the script right. I think I will go ahead and find a freelance consultant who can assist me on this one.

Thanks again and a happy new year :slight_smile:

Kind regards
Morten