none
How to process "Invoke REST Service" response message body

    Question

  • Hi,

    I query a REST Service in SCORCH. It returns a dataset in the "Response Message Body" field something like:

    { "skip" : 0,
      "take" : 1000,
      "rows" : [
      {
        "EmployeeId": "1",
        "PersonId": "1",
        "FirstName": "John",
        "Initials": "J.L.",
        "BirthName": "Doe",
        "Mail": "john.doe@contoso.com"
      },
      {
        "EmployeeId": "2",
        "PersonId": "2",
        "FirstName": "Jane",
        "Initials": "J.",
        "BirthName": "Doe",
        "Mail": "jane.doe@contoso.com"
      }
    ]}

    I can't find anything on the net on how to process this data in Orchestrator. I'm just starting to use it so maybe I don't get it at all. :)

    What I want is a dataset I can iterate through to compare the Mail Address with the UPN in our Active Directory. Any help on how to accomplish this is greatly appreciated!

    Kind regards,

    Erwin

    Friday, March 17, 2017 3:23 PM

Answers

  • Hi Erwin,

    you can use such a PowerShell script with the "Run .Net Script" Activity:

    $Json = @'
    { "skip" : 0,
      "take" : 1000,
      "rows" : [
      {
        "EmployeeId": "1",
        "PersonId": "1",
        "FirstName": "John",
        "Initials": "J.L.",
        "BirthName": "Doe",
        "Mail": "john.doe@contoso.com"
      },
      {
        "EmployeeId": "2",
        "PersonId": "2",
        "FirstName": "Jane",
        "Initials": "J.",
        "BirthName": "Doe",
        "Mail": "jane.doe@contoso.com"
      }
    ]} 
    '@
     
     
    $parsed = (ConvertFrom-Json -InputObject $Json).rows
    
    $EmployeeId = @()
    $PersonId = @()
    $FirstName = @()
    $Initials = @()
    $BirthName = @()
    $Mail = @()
    
    foreach($line in $parsed){
       $EmployeeId += $line.EmployedID
       $PersonId += $line.PersonId
       $FirstName += $line.FirstName
       $Initials += $line.Initials
       $BirthName += $line.BirthName
       $Mail += $line.Mail
    }

    and define the Published Data there:

    This will produce such a result:

    ! ConvertFrom-Json is not available in Powershell-Version 2. You can get rid of with this: http://sc-orchestrator.eu/run-the-powershell-version-of-windows-executing-the-orchestrator-runbook-service-in-run-net-script-activity/

    Reagrds,

    Stefan


    Visit go2azure.eu and my blog at www.sc-orchestrator.eu !

    • Marked as answer by Erwin Vos Saturday, March 18, 2017 7:17 AM
    Friday, March 17, 2017 9:22 PM
    Answerer

All replies

  • Hi Erwin,

    you can use such a PowerShell script with the "Run .Net Script" Activity:

    $Json = @'
    { "skip" : 0,
      "take" : 1000,
      "rows" : [
      {
        "EmployeeId": "1",
        "PersonId": "1",
        "FirstName": "John",
        "Initials": "J.L.",
        "BirthName": "Doe",
        "Mail": "john.doe@contoso.com"
      },
      {
        "EmployeeId": "2",
        "PersonId": "2",
        "FirstName": "Jane",
        "Initials": "J.",
        "BirthName": "Doe",
        "Mail": "jane.doe@contoso.com"
      }
    ]} 
    '@
     
     
    $parsed = (ConvertFrom-Json -InputObject $Json).rows
    
    $EmployeeId = @()
    $PersonId = @()
    $FirstName = @()
    $Initials = @()
    $BirthName = @()
    $Mail = @()
    
    foreach($line in $parsed){
       $EmployeeId += $line.EmployedID
       $PersonId += $line.PersonId
       $FirstName += $line.FirstName
       $Initials += $line.Initials
       $BirthName += $line.BirthName
       $Mail += $line.Mail
    }

    and define the Published Data there:

    This will produce such a result:

    ! ConvertFrom-Json is not available in Powershell-Version 2. You can get rid of with this: http://sc-orchestrator.eu/run-the-powershell-version-of-windows-executing-the-orchestrator-runbook-service-in-run-net-script-activity/

    Reagrds,

    Stefan


    Visit go2azure.eu and my blog at www.sc-orchestrator.eu !

    • Marked as answer by Erwin Vos Saturday, March 18, 2017 7:17 AM
    Friday, March 17, 2017 9:22 PM
    Answerer
  • Thanks! I didn't expect I needed to call a PowerShell script to just process the json output of the REST service. Calling and processing it from within a PowerShell script seems easier to me :) Now I can continue my quest! Thanks again! Kind regards, Erwin
    Saturday, March 18, 2017 7:20 AM