A powershell script ('query over time') to repeat a query against Visual Studio Online as it would have appeared on multiple days

qot.ps1

#
# QoT = Query Over Time. Take a JSON query for VSO and rerun it with several different AsOf dates, and dump the output to the pipeline

# Normal usage would be qot.ps1 -user edyoung -accessToken secrettoken -project SBS -team skype -startdate "8/1/2015" -enddate "9/1/2015" | ConvertTo-Csv  -NoTypeInformation > bugdata.csv

param(
    $user,
    $accessToken,
    $project,
    $team,
    $startdate,
    $enddate)

# $user needs to generate a personal access token with sufficient rights to read the work items in the repository
# We authenticate using basic auth with the username + that access token
$script:encoded = [Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes("$($user):$accessToken"))

Write-Host "user: $user token: $accessToken encoded: $encoded"

# get a list of matching bugs at a particular time
function Get-IDList
{
    param(
        $team,
        $project,
        $date
    )

    # customize this query to return other kinds of info
    $query = "{ `"query`": `"Select [System.ID] FROM WorkItems where [System.WorkItemType] = 'Bug' and [State] <> 'Closed' AsOf '$date'`"}"

    $result = Invoke-WebRequest -Uri "https://$team.visualstudio.com/defaultcollection/$project/_apis/wit/wiql?api-version=1.0" -Body $query `
         -ContentType "application/json" -Method POST -Headers @{Authorization="Basic $script:encoded"}

    $jsonResults = ConvertFrom-Json $result.Content

    return $jsonResults.workItems.id
}

# retrieve the other columns for the set of IDs
function Get-AllItems
{
    param(
        $team,
        $project,
        $ids,
        $pagesize,
        [DateTime]$date)

    for($i=0; $i -lt $ids.length; $i+= $pagesize){ 
        $page = $ids[$i .. ($i+$pagesize-1)]
        
        $idsToFetch = $page -join ","

        $utcdate = $date.ToUniversalTime()
        $dateString = $utcdate.ToString("O")
        # You can customize the fields you want here. 
        $url = "https://$team.visualstudio.com/defaultcollection/_apis/wit/WorkItems?ids=$idsToFetch&fields=System.Id,System.CreatedDate,System.AreaPath,System.State&asOf=$dateString&api-version=1.0"

        $result = Invoke-WebRequest -Uri $url -Headers @{Authorization="Basic $script:encoded"}
        $jsonResults = ConvertFrom-Json $result.Content
        foreach($item in $jsonResults.value)
        {
            # Annotate the results with the AsOf date and the item Age 
            Add-Member -Type NoteProperty -InputObject $item.fields -Name AsOf -Value $date
            Add-Member -Type NoteProperty -InputObject $item.fields -Name Age -Value ($date - ([DateTime]($item.fields.'System.CreatedDate'))).TotalDays
        }
        $jsonResults.value
    }   
}

# rerun the query repeatedly for each day between startdate and enddate 
function Get-QueryOverTime
{
    param($team, $project, [DateTime]$startdate, [DateTime]$enddate)

        while($startdate -le $enddate)
        {
            $ids = Get-IDList -team $team -project $project -date $startdate

            $values = Get-AllItems -team $team -project $project -ids $ids -pagesize 200 -date $startdate

            # we just output everything to the pipeline
            $values.fields
            $startdate = $startdate.AddDays(1)
        }
}


Get-QueryOverTime -team $team -project $project -startdate $startdate -enddate $enddate

You can create a personal access token for use with this script from: https://$team.visualstudio.com/_details/security/tokens