Before we get going it is probably first worth me pointing out (in case you are wondering) that the whole premise of this post stems from a lack of native Azure DevOps PowerShell Module. Yes there are a few solutions out there, but at the time of writing there is no official Microsoft PowerShell DevOps module, so we are stuck with using the CLI if you want to avoid using these other solutions.
In my post Using Azure CLI to query Azure DevOps I explained how you can use the Azure CLI to query Azure DevOps so you can obtain useful information on builds, releases, and other useful information. The solution required a certain level of skill with JMESPath to manipulate your result sets -which as explained can be a little confusing.
However once you have a bare bones result set, it is likely that you will want to consume these results in a more user-friendly environment such as PowerShell so that you can build upon these data sets. I thought this would be an easy thing to do, but as you will see below it was anything but.
A simple example
Lets write a simple Azure CLI query with JMESPath and assign the results to a PowerShell variable:$releases = az pipelines release list --query "[].{name:name,pipeline:releaseDefinition.name}" --output table
If we take a quick look at the contents of the $releases variable we get the following result set:
PS C:\> $releases Name Pipeline ------------ ---------------------------------------------- Release-12 Databricks Pipeline Release-11 Databricks Pipeline Release-10 Databricks Pipeline
The problem is that this result set is coming across as an array of values rather than a tabular data-set and we can see this if we try to select a column:
PS C:\> $releases|Select-Object Name Name ----
ConvertFrom-String
After lots of trial and error and frustrating attempts to arrive at a solution which I would have expected to be easy to get around, I was given a great tip from Jonathan Allen (t) to try and use the ConvertFrom-String cmdlet. Using this on its own gives us the following:
PS C:\> $releases | ConvertFrom-String -PropertyNames Name, Pipeline Name Pipeline ------------ -------- Name Pipeline ------------ ---------------------------------------------- Release-12 Databricks Release-11 Databricks Release-10 Databricks Release-12 Pipeline Release-11 Pipeline Release-10 Pipeline
Clearly this looks like we are getting closer so I tried removing the header and adding an explicit delimiter into this cmdlet:
PS C:\> $releases = $releases | where-object {($_.tostring() -ne "------------ ----------------------------------------------" -And $_.tostring() -ne "Name Pipeline")}
PS C:\> $releases | ConvertFrom-String -PropertyNames Name, Pipeline -Delimiter " "
Name Pipeline
---- --------
Release-12 Databricks Pipeline
Release-1 Databricks Pipeline
Unfortunately due to the fact we do not have a consistent delimiter due to the change in the first column width when our result number changes in digit size we get inconsistent formatting results as exampled above.
Back to basics
I have always been told that you should try and use PowerShell pipeline functionality to keep code brief and optimal, but try as I might I could not arrive at an acceptable solution. In the end I decided to try and return to programming basics and write code to format the result sets as I would in C# or other procedural languages.
So in a nutshell, I decided to do the following:
- Create a table, with correct number of columns
- Create DevOps result set array and strip the header
- Iterate through the array and add a new row to our table containing data for each column
- Manipulate our table in PowerShell as we want
The full solution was as follows:
$releases = az pipelines release list --query "[].{name:name,pipeline:releaseDefinition.name}" --output table
$table = New-Object System.Data.DataTable
$table.Columns.Add("Name")
$table.Columns.Add("Pipeline")
# strip headers and column string
$releases = $releases | where-object {($_.tostring() -ne "------------ ----------------------------------------------" -And $_.tostring() -ne "Name Pipeline")}
$break = 14 # where 14 is the breakpoint between columns
foreach ($row in $releases)
{
#$row
$column1 = $row.ToString().Substring(0,$break)
$column2 = $row.ToString().Substring($break,$row.ToString().Length - $break)
$table.Rows.Add($column1, $column2) | Out-Null # Out-Null required to avoid echo to screen
}
$table | select Name | where {($_.Pipeline -eq "Databricks Pipeline")} | ft
And the last line of this code simply selects the Name of the last three releases where the pipeline was called Databricks Pipeline (proving that our result set is tabular).
Name ------------ Release-12 Release-11 Release-10
Conclusion
As you can see, in those situations where our result sets are not tabular at the point of assignment to a PowerShell variable, it is still possible to convert them as you see fit. Whilst this is a little painful to initially set up (depending upon the number of columns you might have), it is a fairly repeatable pattern. It is disappointing that I could not find a pure pipeline driven solution (and I did try many different techniques including formatting strings), but if you are aware of a better solution, please let me know! If I come across a better solution myself I will update this post accordingly!
In previous posts, I have touched upon the use of Azure Cloud Shell for generic querying of Azure resources and I thought it would be useful to quickly document its use for something a little more specific such as querying or manipulating 
