Format JSON Output to Readable Text

Welcome Forums General PowerShell Q&A Format JSON Output to Readable Text

Viewing 8 reply threads
  • Author
    Posts
    • #200636
      Participant
      Topics: 16
      Replies: 29
      Points: 226
      Rank: Participant

      Hello,

      I have the following PS line that outputs to JSON format:

      
      $RefreshHistoryURL = ‘groups/’ + ‘workspace_id’ + ‘/datasets/’ + ‘dataset_id’ + ‘/refreshes’
      
      Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertTo-Json
      
      

      There’s a little more to it than that, but this covers the gist of things.

      Any ideas on how I can make the JSON output readable?  I tried a few things, but it’s not readable.

      Thanks,

      Frank

      • This topic was modified 4 weeks ago by anelliaf38.
      • This topic was modified 4 weeks ago by anelliaf38.
    • #200648
      Participant
      Topics: 16
      Replies: 29
      Points: 226
      Rank: Participant

      If I use the following variable and the ‘ConvertFrom-Json’ cmdlet, it will display in a table format:

      
      $j = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertFrom-Json
      
      

      The output is two columns, where the ‘value’ column has all the data I need.  Now I’m unsure how to manipulate that column to pull the info.

    • #200651
      Participant
      Topics: 11
      Replies: 1445
      Points: 1,783
      Helping Hand
      Rank: Community Hero

      Any ideas on how I can make the JSON output readable?  I tried a few things, but it’s not readable.

      What exactly does ‘readable’ mean? My assumption is that Invoke-PowerBiRestMethod is a wrapper for Invoke-RestMethod. When Invoke-RestMethod receives a JSON response, it will automatically convert JSON to a Powershell object and then you are converting it back to JSON. If you are simply wanting to parse JSON to look a specific way or get something specific, then post an example of the JSON you are receiving, what is you are seeing and what the expected outcome would be.

    • #200666
      Participant
      Topics: 16
      Replies: 29
      Points: 226
      Rank: Participant

      Yes, I want to parse the output being returned.

      Here’s an example:

      
      @odata.context value
      ————– —–
      http://wabi-us-redirect.analysis.windows.net/v1.0/myorg/groups/29-b01e-597c030859c6/$metadata#refreshes {@{id=911995; refreshType=Scheduled; startTime=2020-01-29T14:02:10.947Z; endTime=2020-01-29T14:11:34.027Z; status=Com…
      
      

      The values I need are under the ‘value’ column.

      I was able to run the following to output to a table format:

      
      $j = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertFrom-Json
      
      $data = $j.value | ft
      
      

      Thanks!

      • This reply was modified 4 weeks ago by anelliaf38.
    • #200942
      Participant
      Topics: 16
      Replies: 29
      Points: 226
      Rank: Participant

      I still have more to do with this, and I’m going a different route.  I have to report the refresh history of PowerBI datasets.  PowerBI has multiple workspaces, which in turn have multiple datasets.  We want to grab the refresh history of the datasets, but we need to use the invoke REST API method to pull the data.

      In order to do so, you need to grab the Workspace ID, then each Dataset ID under that workspace.  The code I have pulls the datasets from 1 workspace and builds the URL needed to use in the invoke-powerbirestmethod:

      
      $GetWorkspace = Get-PowerBIWorkspace -Id a1009c4e-3a7b-4629-b01
      
      $GetDataset = Get-PowerBIDataset -WorkspaceId a1009c4e-3a7b-4629-b01
      
      $vDatasets = $GetDataset.Id
      
      # create array to store output
      $OutputArr = @()
      
      # displays each dataset
      $findDatasets = ForEach ($i in $vDatasets )
      {
      $RefreshHistoryURL = ‘groups/’ + ‘a1009c4e-3a7b-4629-b01’ + ‘/datasets/’ + $i + ‘/refreshes’
      #Write-Host ($i)
      $output = $OutputObj = New-Object -TypeName PSobject
      $outputObj | Add-Member -MemberType NoteProperty -Name URL -Value $RefreshHistoryURL
      
      $OutputArr += $OutputObj
      Write-Verbose $OutputObj
      
      if ($OutputArr.Count -gt 0) {
      Write-Host($jOutput = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertFrom-Json)
      }
      else {
      Write-Host(“0”)
      }
      }
      
      

      The first part of the loop works correctly and the output displays the URL needed for each dataset.  However, I need to grab those URLs and build a command such as:

      
      $jOutput = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertFrom-Json
      
      

      For some reason, I’m having difficulty building this string for each dataset.  Once each string is built, then I need to capture the values for the refresh history.

      Thanks for any help or advice.

      Frank

    • #200963
      Participant
      Topics: 11
      Replies: 1445
      Points: 1,783
      Helping Hand
      Rank: Community Hero

      Try something like this:

      $id = 'a1009c4e-3a7b-4629-b01'
      $Workspace = Get-PowerBIWorkspace -Id $id
      $dataset = Get-PowerBIDataset -WorkspaceId $id
      
      $results = foreach ( $ds in ($dataset | Select-Object -ExpandProperty Id) ) {
          $RefreshHistoryURL = 'groups/{0}/datasets/{1}/refreshes' -f $id, $ds
          Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get
      }
      
    • #201696
      Participant
      Topics: 16
      Replies: 29
      Points: 226
      Rank: Participant

      The loop you wrote only returns the refresh history for 1 dataset instead of the 4 under that workspace.  It returns the necessary values, but many workspaces have multiple datasets.

    • #201708
      Participant
      Topics: 11
      Replies: 1445
      Points: 1,783
      Helping Hand
      Rank: Community Hero

      The loop you wrote only returns the refresh history for 1 dataset instead of the 4 under that workspace.  It returns the necessary values, but many workspaces have multiple datasets.

      I am not testing the code, just providing suggestions. If this were in a function, I’d use Write-Verbose and Write-Host to debug and see that it is looping through the datasets. I’d also see what is returned from the Invoke and if it was similar to your 3rd post, you’d need to get the value of that response. If there are multiple workspaces that have multiple datasets then it would be something like this:

      $id = 'a1009c4e-3a7b-4629-b01'
      $Workspace = Get-PowerBIWorkspace -Id $id
      
      $results = foreach ( $ws in ($Workspace | Select-Object -ExpandProperty Id ) ) {
      
          $dataset = Get-PowerBIDataset -WorkspaceId $ws
      
          foreach ( $ds in ($dataset | Select-Object -ExpandProperty Id) ) {
              Write-Host 'Processing dataset {0}' -f $ds
              $RefreshHistoryURL = 'groups/{0}/datasets/{1}/refreshes' -f $id, $ds
              $response = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get
      
              $response.value
          }
      }
      
      $results
      
    • #201762
      js
      Participant
      Topics: 28
      Replies: 760
      Points: 2,140
      Helping Hand
      Rank: Community Hero

      Convertto-json has a default depth of 2.

      • This reply was modified 3 weeks, 1 day ago by js.
      • This reply was modified 3 weeks, 1 day ago by js.
Viewing 8 reply threads
  • You must be logged in to reply to this topic.