Convert Azure DevOps rest api test plans for all projects to csv file using powershell

Question

I am trying to generate report for all the test plans available in azure DevOps organization. here is my script which generates list of projects and the iterate through every project to find test plans available in it. I want all this data to be saved in csv file. I am able to get json file. Is there any way I can get this data saved in csv file with every project iterate for test plan ?

$connectionToken = ""
$BaseUrl = "https://dev.azure.com/{organization_name}/_apis/projects?  
api-versions=5.0"

$base64AuthInfo= 
 [System.Convert]::ToBase64String([System.Text.Encoding]
 '::ASCII.GetBytes(":$($connectionToken)"))

$ProjectInfo = Invoke-RestMethod -Uri $BaseUrl -Headers     
@{authorization = "Basic $base64AuthInfo"} - 
Method Get

$ProjectDetails = $ProjectInfo | ConvertTo-Json -Depth 100

$Projectname = $ProjectInfo.value.name 

ForEach ($project in $Projectname){

$TestPlanApi = "https://dev.azure.com/{org}/$project/_apis/test/plans?   
api-version=5.0"

$TestplanInfo = Invoke-RestMethod -Uri $TestPlanApi -Headers   
@{authorization = "Basic $base64AuthInfo"} -Method Get

  if (-NOT ($TestplanInfo.count -eq 0)){   
   
   $info = $TestplanInfo | ConvertTo-Json -Depth 100

   $info

 }
 }

This gives me following json file , I want to convert in to csv. Due to every project test plans value starts the Json result with value I am not able to expand it and save to csv

 {
   "value": [
     {
       "id": 134,
        "name": "sprint1",
        "url": "https://dev.azure.com/fabrikam/fabrikam-fiber- 
         tfvc/_apis/test/Plans/1",
         "project": {
            "id": "eb6e4656-77fc-42a1-9181-4c6d8e9da5d1",
             "name": "Fabrikam-Fiber-TFVC",
             "url":    
             "https://dev.azure.com/fabrikam/_apis/projects/Fabrikam-                         
             Fiber- 
                   TFVC"
           },
          "area": {
            "id": "343",
            "name": "Fabrikam-Fiber-TFVC"
           },
          "iteration": "Fabrikam-Fiber-TFVC\\Release 1\\Sprint 1",
          "state": "Active",
          "rootSuite": {
            "id": "1"
          },
          "clientUrl": "mtms://fabrikam.visualstudio.com:443/DefaultCollection/p:Fabrikam- 
          Fiber-TFVC/Testing/testplan/connect?id=1"
       }
     ],
     "count": 1
    }
    {
      "value": [
        {
          "id": 567,
          "name": "sprint1",
          "url": "https://dev.azure.com/fabrikam/fabrikam-fiber- 
           tfvc/_apis/test/Plans/1",
          "project": {
             "id": "eb6e4656-77fc-42a1-9181-4c6d8e9da5d1",
             "name": "Fabrikam-Fiber-TFVC",
             "url": "https://dev.azure.com/fabrikam/_apis/projects/Fabrikam-Fiber- 
                    TFVC"
            },
            "area": {
               "id": "343",
               "name": "Fabrikam-Fiber-TFVC"
             },
             "iteration": "Fabrikam-Fiber-TFVC\\Release 1\\Sprint 1",
             "state": "Active",
             "rootSuite": {
                "id": "1"
             },
             "clientUrl":"mtms://fabrikam.visualstudio.com:443/DefaultCollection/p:Fabrikam- 
             Fiber-TFVC/Testing/testplan/connect?id=1"
          },
          {
          "id": 678,
          "name": "sprint1",
          "url": "https://dev.azure.com/fabrikam/fabrikam-fiber- 
             tfvc/_apis/test/Plans/1",
          "project": {
            "id": "eb6e4656-77fc-42a1-9181-4c6d8e9da5d1",
            "name": "Fabrikam-Fiber-TFVC",
            "url": "https://dev.azure.com/fabrikam/_apis/projects/Fabrikam-Fiber- 
                 TFVC"
           },
           "area": {
             "id": "343",
             "name": "Fabrikam-Fiber-TFVC"
           },
           "iteration": "Fabrikam-Fiber-TFVC\\Release 1\\Sprint 1",
           "state": "Active",
           "rootSuite": {
             "id": "1"
           },
           "clientUrl": 
           "mtms://fabrikam.visualstudio.com:443/DefaultCollection/p:Fabrikam- 
            Fiber-TFVC/Testing/testplan/connect?id=1"
         }
        ],
        "count": 2
       }

These are the values for different projects test runs, some projects have count = 1 then it shows one id , some projects has count =3 then it shows all the 3 ids and so on

I want this json file in csv file with columns -

id , name , url , project.name , project.id , project.url ,area.id , area.name , iteration , owner , revision , state , rootsuite.id , clienturl

How can I expand all the values in csv file ? I tried

Select-object Expand-property value but its fails to to expand all the values in json data

You would normally use Get-Content -raw and ConvertFrom-Json, but the example isn't valid json. — OwlsSleeping, Oct 20 '20 at 22:25
Indeed: please make sure that the sample JSON is valid, in the spirit of providing a [mcve]. — mklement0, Oct 20 '20 at 22:43
@this is the valid json file , I am getting this response from azure devops test plan rest api , it has 100’s of test plans it provides result in this format for every project with all available test plans. — megha, Oct 20 '20 at 22:56
It isn't valid JSON, as evidenced by lines such as `"count" = 2` — mklement0, Oct 21 '20 at 01:24
@mklement0 I updated this output from https://learn.microsoft.com/en-us/rest/api/azure/devops/test/test%20%20plans/list?view=azure-devops-rest-5.0 here they are calling one project and I have 100 project. I tried to recreate my result based on this and mistakenly edited wrong section. I have updated question with script as well now. — megha, Oct 21 '20 at 01:39
The linked post contains valid JSON. Your attempt to generalize it is still not valid JSON. Please make sure that your sample can be parsed with `ConvertFrom-Json`. Aside from that, the solution will be analogous to the one given in response to your previous question: use `Select-Object` with _calculated properties_: https://stackoverflow.com/a/64364176/45375 — mklement0, Oct 21 '20 at 12:57
Hi @megha, Just checking in to see whether this issue is still blocking you now? Any update for this issue? — Vito Liu, Oct 22 '20 at 07:03
@VitoLiu-MSFT I am able to get csv file for every project as per you solution but this gives me large number of csv files. I wanted all test plans for all projects in one csv file. I am still struggling to get this. As I can see everyone in here mentioned I won't be able to get all data in one file. I will accept this as answer. Thank you — megha, Oct 22 '20 at 12:57

Vito Liu · Accepted Answer · 2020-10-22T07:05:29.543

As a workaround, we can save the response body and then convert the json file to csv file.

We cannot save all info in one csv file, the latest info will overwrite the old data, so we need to save the test plan info in different csv files.

Sample:

$connectionToken = "{PAT}"
$BaseUrl = "https://dev.azure.com/{Org}/_apis/projects?api-version=6.1-preview.4"

$base64AuthInfo= [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($connectionToken)"))

$ProjectInfo = Invoke-RestMethod -Uri $BaseUrl -Headers @{authorization = "Basic $base64AuthInfo"} -Method Get

$ProjectDetails = $ProjectInfo | ConvertTo-Json -Depth 100

$Projectname = $ProjectInfo.value.name 

ForEach ($project in $Projectname){

$TestPlanApi = "https://dev.azure.com/{Org}/$project/_apis/test/plans?api-version=5.0"

$TestplanInfo = Invoke-RestMethod -Uri $TestPlanApi -Headers @{authorization = "Basic $base64AuthInfo"} -Method Get 
if (-NOT ($TestplanInfo.count -eq 0)){       
   
   #Save the test plan info to json file
   $info = $TestplanInfo | ConvertTo-Json -Depth 100 | out-file E:\test\$project.json
   #convert the json file to csv file 
   Get-Content -Path E:\test\$project.json  | ConvertFrom-Json | Select-Object -expand value | ConvertTo-Csv -NoTypeInformation | Set-Content E:\test\$project.csv
   #delete json file
   Remove-Item E:\test\$project.json
 }
 }

Result:

Convert Azure DevOps rest api test plans for all projects to csv file using powershell

1 Answers1