In Azure Devops, how can I query all the user stories, and the count of related bugs for each story?

You can set up a pipeline with the scheduled triggers. And in the pipeline, you can call the Azure DevOps REST API "Send Mail – Send Mail" to send email for the query result to the specified users.

Below is the sample with the detailed steps to set up the configurations:

1. Create a Work Item query like as below image shows. It will returns all the User Stories and the linked Bugs of them within a project. From the URL of this query, you can see its ID.

   

   The WIQL of this query looks like below. It will be used when calling the "Send Mail" API.

   select [System.Id], [System.WorkItemType], [System.State], [System.Title] from WorkItemLinks where (Source.[System.TeamProject] = '{project-name}' and Source.[System.WorkItemType] = 'User Story') and (Target.[System.TeamProject] = '{project-name}' and Target.[System.WorkItemType] = 'Bug') order by [System.Id] mode (MayContain)
   

2. In the project where the Work Item query is in, have a repository that contains a pipeline YAML file and a PowerShell script file.

   

3. The content of PowerShell script (wiql-result-send-email.ps1).

param (
    [Alias("org", "o")]
    [string] $organization,

    [Alias("proj", "p")]
    [string] $project,

    [Alias("qid")]
    [string] $queryId,

    [Alias("mail", "ma")]
    [string[]] $emailAddresses
)

$headers = @{
    Authorization = "Bearer $env:SYSTEM_ACCESSTOKEN"
    "Content-Type" = "application/json"
}

$uri_query = "https://dev.azure.com/${organization}/${project}/_apis/wit/queries/${queryId}?api-version=7.1"
$queryName = (Invoke-RestMethod -Method GET -Uri $uri_query -Headers $headers).name

$uri_query_result = "https://dev.azure.com/${organization}/${project}/_apis/wit/wiql/${queryId}?api-version=7.1"
$query_result = Invoke-RestMethod -Method GET -Uri $uri_query_result -Headers $headers

$mailMessage = "[User Story ID], [Linked Bug Count]"
$stories = $query_result.workItemRelations | Where-Object { $null -eq $_.source }
foreach ($story in $stories)
{
    $storyId = $story.target.id
    $bugCount = ($query_result.workItemRelations | Where-Object { $_.source.id -eq $storyId }).count
    $mailMessage += "n${storyId}, ${bugCount}"
}

$userIds = @()
foreach ($emailAddress in $emailAddresses)
{
    $get_userId = "https://vssps.dev.azure.com/${organization}/_apis/identities?searchFilter=General&filterValue=${emailAddress}&queryMembership=None&api-version=7.1"
    $userId = (Invoke-RestMethod -Method GET -Uri $get_userId -Headers $headers).value[0].id
    $userIds += $userId
}

$uri_send_mail = "https://dev.azure.com/${organization}/${project}/_apis/wit/sendmail?api-version=7.1"
$body_send_mail = (@{
    fields = @(
        "System.Id"
        "System.WorkItemType"
        "System.State"
        "System.Title"
    )
    message = @{
        subject = "Result of Query: ${queryName}"
        body = $mailMessage
        to = @{
            tfIds = $userIds
        }
        cc = @{
            tfIds = $userIds
        }
        replyTo = @{
            tfIds = $userIds
        }
    }
    wiql = "select [System.Id], [System.WorkItemType], [System.State], [System.Title] from WorkItemLinks where (Source.[System.TeamProject] = '${project}' and Source.[System.WorkItemType] = 'User Story') and (Target.[System.TeamProject] = '${project}' and Target.[System.WorkItemType] = 'Bug') order by [System.Id] mode (MayContain)"
} | ConvertTo-Json -Depth 10).Replace("\n", "n")

Invoke-RestMethod -Method POST -Uri $uri_send_mail -Headers $headers -Body $body_send_mail

4. The content of the pipeline YAML (wiql-result-send-email.yml).

trigger: none
schedules:
- cron: '0 0 * * *'
  displayName: 'Daily Send'
  branches:
    include:
    - main

parameters:
- name: organization
  displayName: 'Organization Name:'
  type: string
  default: 'myOrg'
- name: project
  displayName: 'Project Name:'
  type: string
  default: 'myProj'
- name: query
  displayName: 'Query ID:'
  type: string
  default: '88064aa6-63fe-47fa-9486-46bb809c84c4'
- name: emails
  displayName: 'List of email addresses (comma separated):'
  type: string
  default: >
    '[email protected]',
    '[email protected]'

steps:
- task: PowerShell@2
  displayName: 'Send Query Result Email'
  env:
    SYSTEM_ACCESSTOKEN: $(System.AccessToken)
  inputs:
    pwsh: true
    filePath: 'scripts/wiql-result-send-email.ps1'
    arguments: >
      -o "${{ parameters.organization }}"
      -p "${{ parameters.project }}"
      -qid ${{ parameters.query }}
      -ma ${{ parameters.emails }}

5. Use the YAML file wiql-result-send-email.yml to create a YAML pipeline.

   • Since the pipeline has been configured a scheduled trigger, it will automatically trigger a run of this pipeline everyday. at this time, the run will use the default values of parameters and pass them into the PowerShell script for use.

   • If you manually trigger this pipeline, you can enter different values to override the default ones.

     

6. The content of the email message will look like as below.