Tag: Azure Devops

This PowerShell Script and any future enhancements can be found in my Public GitHub repository – Azure DevOps Work Item Data Extraction.ps1

Working in a Microsoft centric ecosystem, my team at this time utilizes Azure DevOps. For a Reporting project the other day, I had a request where alongside creating a dashboard for data scraped from a 3rd party tool’s API, business users wanted to incorporate data from a particular Azure DevOps Board, to review how quickly certain related Work Item types were being resolved over time.

The below PowerShell script documents how this task was acheived, outputing the desired data as a .json file for modeling in Power BI, and is relatively simple overall – in my opinion.

We utilize Work Item Query Language (WIQL) to get the fields we care about through the DevOps REST API call.

Steps:

1. Navigate to the Project of interest in your Azure Devops. Make note of your {organization}, {project}, and {team} in DevOps. These strings will need to be added to the below script as variables.
   
2. In the top-right, select the User Settings icon and go to Personal access tokens. Assuming you have never set one up before, create a new token. For this example, I have given the access token a name, selected my {Organization}, specified access as being Full, and custom defined it to expire the maximum number of days I can specify (1 year). You can customize this to better fit your security and expiration preferences.
   
   
   
   
   
   
   
   Make note of this token ID as once you leave the screen you cannot retrieve it again, and thereby must create an entirely new token.

3. Paste the token value and other appropriate strings into variables. Paste the copied token value into the {$token} variable, and paste your noted {$organization}, {$project}, and {$team} strings into their appropriate variables. Specify where the exported data should be saved to in the {$exportFileLocation} variable. The example below utilizes my Downloads folder.
   
4. Build your data query criteria. In the {$body} variable, build out the criteria that determines what work items you want data for. In my case I was looking for work items with the custom type of “Vulnerability” that had been completed, or, in state of “Done”.
   
   A quick note on this:
   
   The WHERE clause is the important part of the query inside the {$body} variable. By REST API design, the return of this data call will only contain the Work Item ID, and the Work Item URL – regardless of what columns you specify in the SELECT clause.
   

5. Elect field data columns to export. Customize the {$customObject} and {$exportData} variables to output the fields you wish to export for found work items matching your query. Working inside PowerShell ISE, auto-complete helped me discover a number of available fields, both default and custom-defined in my custom work item type.

And that is pretty much it.

Assuming the user you run this script as has full access to the work items you are trying to query, each work item matching your criteria will be looped through to retrieve details and then exported as a .json file to your specified location.

This PowerShell Script and any future enhancements can be found in my Public GitHub repository – Azure DevOps Work Item Data Extraction.ps1

CHALLENGE

One notable challenge I ran into with this project is that the v6.0 of this DevOps REST API document mentions you should be able to query multiple work items at once and, retrieve the field data for each. This was not something I could get to work, as each call returned empty field data: “fields=;”

Working: $url = “https://dev.azure.com/$organization/$project/_apis/wit/workitems/12345?`$expand=All&api-version=5.0”
Not Working: $url = “https://dev.azure.com/$organization/$project/_apis/wit/workitems?ids=46477,43819&api-version=6.0”

I will update in the future if I figure out how to get this to work, but for now simply iterate through each returned work item matching query criteria, and 1 ID at a time retrieve the field data therein.

# Script:        Get DevOps WorkItem Data
# Last Modified: 4/10/2022 3:30 PM
#
# Note: If copying from Repo, add in your Personal-Access-Token (PAT) and sepcify export file location.
#--------------------------------------------------------------------------------
# 1. Use WIQL to get a queried for list of Work Items matching certain conditions
#--------------------------------------------------------------------------------
$token = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

$organization = "AnalyticArchives"
$project = "ProDev"
$team = "ProDev Team"

$exportFileLocation = 'C:\Users\{YourUser}\Downloads'

$codedToken = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$($token)"))
$AzureDevOpsAuthenicationHeader = @{Authorization = 'Basic ' + $codedToken}

$uri = "https://dev.azure.com/$organization/$project/$team/_apis/wit/wiql?api-version=6.0"

$body = @{
    #NOTE: Ignore the specified columns, only returns ID and URL by design
    "query" = "Select [System.Id], 
                      [System.Title], 
                      [System.State],
                      [Custom.FirstDetected] 
                      From WorkItems 
                      Where [System.WorkItemType] = 'Vulnerability' 
                      AND [State] = 'Done'"
} | ConvertTo-Json 

$response = Invoke-RestMethod -Method Post -Uri $uri -Headers $AzureDevOpsAuthenicationHeader -Body $body -ContentType 'application/json'

#--------------------------------------------------------------------------------
# 2. Use returned list of Work Item ID's to get field Data
#--------------------------------------------------------------------------------
$exportData = @()
$tempID = 0
foreach ($item in $response.workItems) {
    $tempID = $item.id
    $url = "https://dev.azure.com/$organization/$project/_apis/wit/workitems/$($tempID)?`$expand=All&api-version=5.0"
    $response = Invoke-RestMethod -Uri $url -Headers $AzureDevOpsAuthenicationHeader -Method Get -ContentType application/json 

    foreach ($field in $response) {

        $customObject = new-object PSObject -property @{

            "WorkItemID" = $field.fields.'System.Id'
            "URL" = $field.url
            "WorkItemType" = $field.fields.'System.WorkItemType'
            "Title" = $field.fields.'System.Title'
            "IterationPath" = $field.fields.'System.IterationPath'
            "State" = $field.fields.'System.State' 
            "AssignedTo" = $field.fields.'System.AssignedTo'.displayName
            "CoAssigned" = $field.fields.'Custom.CoAssigned'.displayName
            "FirstDetected" = $field.fields.'Custom.FirstDetected'
            "DueDate" = $field.fields.'Microsoft.VSTS.Scheduling.DueDate'
            "Priority" = $field.fields.'Microsoft.VSTS.Common.Priority'
            "Effort" = $field.fields.'Microsoft.VSTS.Scheduling.Effort'
            "ChangedDate" = $field.fields.'System.ChangedDate'
            "ChangedBy" = $field.fields.'System.ChangedBy'.displayName
            "ParentWorkItem" = $field.fields.'System.Parent'
            "RelatedWorkItems" = $field.relations
        } 
    $exportData += $customObject      
    }
}

$exportData | Select-Object -Property WorkItemID,
    URL,
    WorkItemType,
    Title,
    IterationPath,
    State,
    AssignedTo,
    CoAssigned,
    FirstDetected,
    DueDate,
    Priority,
    Effort,
    ChangedDate, 
    ChangedBy,
    ParentWorkItem,
    RelatedWorkItems

$exportData | ConvertTo-Json | Out-File "$($exportFileLocation)\DEVOPS_EXPORT.json"