locked
How to Parse JSON response returned from TFS REST API using Powershell RRS feed

  • Question

  • Hi All,

    I am working with Powershell script to get URL for each build or Release definitions and then iterate through each URL to get more details on each build definition and then I want to export that information to EXCEL or CSV. But JSON response is very nested and it contains several levels deep objects and properties. Let's say we have a task in the definition to Copy Files, then I am interested in exporting properties like : SourceFolder, TargetFolder, Content(see below image)

    Has anyone tried to parse it and nicely exported it to CSV? Can someone give me a direction on how to iterate through each object and it's properties given the JSON response below? That will be really appreciated. Thanks in advance.

    Here is my code:

    [void][System.Reflection.Assembly]::LoadFile("C:\00PS\Newtonsoft.Json.dll")
    
    $baseUrl = "http://servername:80/tfs"
    $targetCollection = "URS"
    $targetProject = "ETL_PROJ"
    
    # Get an overview of all build definitions in this team project
    
    $definitionsOverviewUrl = "$baseUrl/$targetCollection/$targetProject/_apis/build/Definitions"
    
    $definitionsOverviewResponse = Invoke-WebRequest -UseDefaultCredentials -Uri $definitionsOverviewUrl
    
    $definitionsOverview = (ConvertFrom-Json $definitionsOverviewResponse.Content).value
    
    foreach($definitionEntry in ($definitionsOverview | Where-Object { $_.name -eq 'AVRC_ETL_MetaData_SQLValidationFramework' }))
    {
        $definitionUrl = $definitionEntry.url
        #Write-Output $definitionUrl ##returns all the build defs URLs
        
        #below lines working    
        $response = Invoke-WebRequest -UseDefaultCredentials -Uri $definitionUrl
        
        #Write-Host $response ##returns JSON response in raw format   
        
        <##Response :
    {"build":[{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"Copy Files to: $(build.artifactstagingdirectory)\\Artifacts\\Source","task":{"id":"5bfb729a-a7c8-4a78-a7c3-8d717bb7c13c","ver
    sionSpec":"*"},"inputs":{"SourceFolder":"$(build.sourcesDirectory)","Contents":"TfsRm_SQL_Table\\*.sql\nTfsRm_SQL_Table_Deltas\\*.sql\nTfsRm_SQL_SP\\*.sql\nTfsRm_SQL_F\\*.sql\nTfsRm_SQL_ConfigData\\*.sql ","T
    argetFolder":"$(build.artifactstagingdirectory)\\Artifacts\\Source","CleanTargetFolder":"false","OverWrite":"false"}},{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"Copy Files to: $(
    DropFolder)\\$(Build.DefinitionName)$(Build.BuildNumber)","task":{"id":"5bfb729a-a7c8-4a78-a7c3-8d717bb7c13c","versionSpec":"*"},"inputs":{"SourceFolder":"$(build.artifactstagingdirectory)\\Artifacts","Conten
    ts":"**","TargetFolder":"$(DropFolder)\\$(Build.DefinitionName)$(Build.BuildNumber)","CleanTargetFolder":"false","OverWrite":"false"}},{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"
    Publish Artifact: drop","task":{"id":"2ff763a7-ce83-4e1f-bc89-0ae63477cebe","versionSpec":"*"},"inputs":{"PathtoPublish":"$(build.artifactstagingdirectory)\\Artifacts","ArtifactName":"drop","ArtifactType":"Co
    ntainer","TargetPath":"\\\\my\\share\\$(Build.DefinitionName)\\$(Build.BuildNumber)"}}],"options":[{"enabled":false,"definition":{"id":"7c555368-ca64-4199-add6-9ebaf0b0137d"},"inputs":{"multipliers":"[]","par
    allel":"false","continueOnError":"true","additionalFields":"{}"}},{"enabled":false,"definition":{"id":"a9db38f9-9fdc-478c-b0f9-464221e58316"},"inputs":{"workItemType":"4089","assignToRequestor":"true","additi
    onalFields":"{}"}},{"enabled":false,"definition":{"id":"57578776-4c22-4526-aeb0-86b6da17ee9c"},"inputs":{"additionalFields":"{}"}}],"triggers":[{"branchFilters":["+$/AVRC_ETL/MicroServices/SQL Validation Fram
    ework"],"batchChanges":true,"maxConcurrentBuildsPerBranch":1,"triggerType":"continuousIntegration"}] ...................
    .
    .
    .
    some more data..
    #>
    
     $buildDefinition = [Newtonsoft.Json.JsonConvert]::DeserializeObject($response.Content)
        #Write-Output $buildDefinition ##Writes output in parsed format deserialized
    
    
      $buildDefinition | Export-CSV C:\Users\Jumbo\Documents\sample.csv 
    
    
     <##Response:
        Type                       : Array
    IsReadOnly                 : False
    HasValues                  : True
    First                      : {enabled, continueOnError, alwaysRun, displayName...}
    Last                       : {enabled, continueOnError, alwaysRun, displayName...}
    Count                      : 3
    Parent                     : {{
                                   "enabled": true,
                                   "continueOnError": false,
                                   "alwaysRun": false,
                                   "displayName": "Copy Files to: $(build.artifactstagingdirectory)\\Artifacts\\Source",
                                   "task": {
                                     "id": "5bfb729a-a7c8-4a78-a7c3-8d717bb7c13c",
                                     "versionSpec": "*"
                                   },
                                   "inputs": {
                                     "SourceFolder": "$(build.sourcesDirectory)",
                                     "Contents": "TfsRm_SQL_Table\\*.sql\nTfsRm_SQL_Table_Deltas\\*.sql\nTfsRm_SQL_SP\\*.sql\nTfsRm_SQL_F\\*.sql\nTfsRm_SQL_ConfigData\\*.sql ",
                                     "TargetFolder": "$(build.artifactstagingdirectory)\\Artifacts\\Source",
                                     "CleanTargetFolder": "false",
                                     "OverWrite": "false"
                                   }
                                 } {
                                   "enabled": true,
                                   "continueOnError": false,
                                   "alwaysRun": false,
                                   "displayName": "Copy Files to: $(DropFolder)\\$(Build.DefinitionName)$(Build.BuildNumber)",
                                   "task": {
                                     "id": "5bfb729a-a7c8-4a78-a7c3-8d717bb7c13c",
                                     "versionSpec": "*"
                                   },
                                   "inputs": {
                                     "SourceFolder": "$(build.artifactstagingdirectory)\\Artifacts",
                                     "Contents": "**",
                                     "TargetFolder": "$(DropFolder)\\$(Build.DefinitionName)$(Build.BuildNumber)",
                                     "CleanTargetFolder": "false",
                                     "OverWrite": "false"
                                   }
                                 } {
                                   "enabled": true,
                                   "continueOnError": false,
                                   "alwaysRun": false,
                                   "displayName": "Publish Artifact: drop",
                                   "task": {
                                     "id": "2ff763a7-ce83-4e1f-bc89-0ae63477cebe",
                                     "versionSpec": "*"
                                   },
                                   "inputs": {
                                     "PathtoPublish": "$(build.artifactstagingdirectory)\\Artifacts",
                                     "ArtifactName": "drop",
                                     "ArtifactType": "Container",
                                     "TargetPath": "\\\\my\\share\\$(Build.DefinitionName)\\$(Build.BuildNumber)"
                                   }
                                 }}
    Root                       : {build, options, triggers, variables...}
    Next                       : 
    Previous                   : 
    Path                       : build
    LineNumber                 : 0
    LinePosition               : 0
    AllowNew                   : True
    AllowEdit                  : True
    AllowRemove                : True
    SupportsChangeNotification : True
    SupportsSearching          : False
    SupportsSorting            : False
    IsSorted                   : False
    SortProperty               : 
    SortDirection              : Ascending
    IsFixedSize                : False
    SyncRoot                   : System.Object
    IsSynchronized             : False
    
    
      
    .
    .
    .
    some more data...
    ..
    #>
    
    }


    Thanks in advance!!

    Thursday, November 30, 2017 4:00 PM

All replies

  • Ok, I think I got it! Here is my solution but if someone else has better idea can let me know. I still have to figure out some details but I think I am on the right track now..Thanks.

    Credit to this post: https://stackoverflow.com/questions/16575419/powershell-retrieve-json-object-by-field-value

        #below lines working    
        $response = Invoke-WebRequest -UseDefaultCredentials -Uri $definitionUrl
        #Write-Output $response ##returns JSON response in raw format   
        
        $z = $response | ConvertFrom-Json
        foreach($environment in $z.environments)
        {
             Write-output $environment.name
            #Write-output $z.environments[0].name
            Write-output $environment.variables.AVRCShare.value
            Write-output $environment.variables.SqlDBServer.value
            
            foreach($task in $environment.deployStep.tasks)
            {
                Write-output $task.inputs.ScriptPath
                Write-output $task.inputs.ScriptArguments
            } 
            <#
            Write-output $z.name
            #Write-output $z.environments[0].name
            Write-output $z.environments[0].variables.AVRCShare
            Write-output $z.environments[0].variables.SqlDBServer
            Write-output $z.environments[0].deployStep[0].tasks[0].inputs.ScriptPath
            Write-output $z.environments[0].deployStep[0].tasks[0].inputs.ScriptArguments#>
          [PSCustomObject]@{
           Environment = $environment.name
           AVRCShare = $environment.variables.AVRCShare 
           SQLServerDB = $environment.variables.SqlDBServer
           ScriptPath = $task.inputs.ScriptPath
           ScriptArguments = $task.inputs.ScriptArguments
    
           } | Export-Csv C:\Users\Documents\Finaloutput.csv  -notype -Append 
    
        } 


    Thanks in advance!!

    Thursday, November 30, 2017 10:37 PM