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

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Morten Schaumann Morten Schaumann 4 months, 3 weeks ago.

  • Author
    Posts
  • #60909
    Profile photo of Morten Schaumann
    Morten Schaumann
    Participant

    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

  • #60952
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #60970
    Profile photo of Daniel Krebs
    Daniel Krebs
    Moderator

    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:
    https://github.com/willcodeforpizza/PoshWeather/blob/master/PoshWeather.psm1

    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.

  • #60987
    Profile photo of Morten Schaumann
    Morten Schaumann
    Participant

    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 🙂

    Kind regards
    Morten

You must be logged in to reply to this topic.