2.3 Work Item Queries, WIQL Charts & Analytics

2.3 Work Item Queries, WIQL Charts & Analytics

Metrics are only actionable when the underlying work items can be retrieved, filtered and charted. Azure Boards exposes two complementary query planes: WIQL for point-in-time queries that drive dashboard widgets, and the Analytics service for the revision history that trend widgets require.

Work Item Query Language (WIQL) in Azure Boards

Work Item Query Language (WIQL) is a SQL-like declarative language used to construct queries that filter and retrieve work items from the Azure Boards database. You can author WIQL queries directly in the web portal or submit them programmatically via the Azure DevOps REST API (POST https://dev.azure.com/{org}/{project}/_apis/wit/wiql?api-version=7.1).

The Three Query Types (Modes)

1. Flat List of Work Items (mode(MustHave)):
   • Returns a single flat table of work items matching specified criteria.
   • AZ-400 Critical Rule: Only Flat List queries can be used to generate Dashboard Charts (Pie, Bar, Column) and Query Tiles.
2. Work Items and Direct Links (mode(MustHave) with Source/Target):
   • Queries parent-child, related, predecessor-successor, or test-to-requirement links.
   • Evaluates relationships between a source work item and target linked items.
3. Tree of Work Items (mode(Recursive)):
   • Queries multi-level hierarchical parent-child relationships (e.g., Epics > Features > User Stories > Tasks).

WIQL Syntax, Clauses & Macros

SELECT [System.Id], [System.Title], [System.State], [System.AssignedTo]
FROM WorkItems
WHERE [System.WorkItemType] = 'Bug'
  AND [System.State] <> 'Closed'
  AND [System.IterationPath] = @CurrentIteration
ORDER BY [Microsoft.VSTS.Common.Priority] ASC, [System.CreatedDate] DESC

Essential Built-In Macros

• @Me: Resolves dynamically to the identity of the currently logged-in user running the query.
• @CurrentIteration: Dynamically evaluates to the current team sprint iteration path based on today's calendar date.
• @CurrentIteration +/- n: Evaluates to relative past or future sprints.
• @Today: Current date (midnight). Supports arithmetic: @Today - 7 (items in the last 7 days).
• @Project: Evaluates to the current team project context.

Key Operators

• UNDER: Performs hierarchical path matching for Area Paths and Iteration Paths (e.g., [System.AreaPath] UNDER 'ECommerce\Payment').
• EVER / WAS EVER: Evaluates historical audit logs. Checks whether a field ever held a specified value in the past (e.g., [System.AssignedTo] EVER @Me).
• CONTAINS WORDS: Performs full-text indexed searches on plain text and HTML fields.

---

Concrete WIQL Implementation Examples

Example 1: Flat Query for High-Severity Active Bugs in Current Sprint

Used for dashboard query tiles with conditional alerting:

SELECT
    [System.Id],
    [System.Title],
    [System.AssignedTo],
    [Microsoft.VSTS.Common.Severity],
    [Microsoft.VSTS.Common.Priority]
FROM WorkItems
WHERE
    [System.TeamProject] = @Project
    AND [System.WorkItemType] = 'Bug'
    AND [System.State] IN ('Active', 'New')
    AND [System.IterationPath] = @CurrentIteration
    AND [Microsoft.VSTS.Common.Severity] IN ('1 - Critical', '2 - High')
ORDER BY
    [Microsoft.VSTS.Common.Severity] ASC,
    [System.CreatedDate] ASC

Example 2: Direct Links Query for User Stories Lacking Test Cases

Used to identify test coverage gaps before sprint closure:

SELECT
    [System.Id],
    [System.Title],
    [System.State]
FROM WorkItemLinks
WHERE
    (
        [Source].[System.TeamProject] = @Project
        AND [Source].[System.WorkItemType] = 'User Story'
        AND [Source].[System.State] <> 'Closed'
        AND [Source].[System.IterationPath] = @CurrentIteration
    )
    AND ([System.Links.LinkType] = 'Microsoft.VSTS.Common.TestedBy-Forward')
    AND ([Target].[System.WorkItemType] = 'Test Case')
MODE (DoesNotContain)

Example 3: Tree Query for Feature and Task Hierarchy

Used for backlog decomposition reviews:

SELECT
    [System.Id],
    [System.Title],
    [System.WorkItemType],
    [System.State],
    [System.AssignedTo]
FROM WorkItemLinks
WHERE
    (
        [Source].[System.TeamProject] = @Project
        AND [Source].[System.WorkItemType] IN ('Feature', 'User Story')
        AND [Source].[System.AreaPath] UNDER 'ECommerce\Core'
    )
    AND ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward')
    AND ([Target].[System.WorkItemType] IN ('User Story', 'Task', 'Bug'))
MODE (Recursive)

---

Query Charts, Dashboards & Automated Alerts

Query-Based Dashboard Charts

Once a Flat List query is saved under Shared Queries (queries under My Queries cannot be shared with team dashboards), you can configure visual widgets:

• Chart for Work Items Widget: Renders Pie charts (e.g., Work items by State), Bar charts (Work items by Assignee), or Pivot tables (Severity vs. State).
• Query Tile Widget: Displays total item count. You can set conditional rules: if count is 0, display green; if count > 0, display red.

Query-Based Alerts & Notifications

Teams can configure event notifications under Project Settings > Notifications using WIQL filter criteria:

• Custom Work Item Alerts: Automatically email engineering leads or post to Microsoft Teams whenever a Sev-1 Bug is logged or when an item transitions to Blocked.

---

Analytics Views, OData and Query Macros

Work Item Query Language answers "which items match right now"; the Analytics service answers "how did this change over time". Analytics stores a nightly-plus-near-real-time historical snapshot of every work item revision, which is what makes trend widgets such as Cumulative Flow Diagram, Velocity, Lead Time and Cycle Time possible. A flat WIQL query cannot produce those charts because it has no revision history.

• Analytics views are saved, filtered datasets (project, work item types, fields, history window) published to Power BI through the Power BI Data Connector. Use them when leadership wants a cross-project executive report that outlives the Azure DevOps dashboard.
• OData endpoints (https://analytics.dev.azure.com/{org}/{project}/_odata/v4.0-preview/WorkItems) support $filter, $apply and groupby aggregations. OData is the correct answer whenever a question asks for a rolled-up count or average across teams without building a widget.
• Rolling history is bounded by the view's "history" setting; a view configured for the last 90 days cannot answer a 12-month lead-time question.

Query macros keep dashboards portable across sprints and teams:

@CurrentIteration resolves against a team context, so a query saved under Shared Queries without an explicit team parameter returns the wrong sprint when a second team opens it. Pass the team explicitly, as in @CurrentIteration('[Contoso]\Payments-Core'), whenever a query is shared across teams.

---

AZ-400 Exam Pitfalls & Traps

1. Attempting to Create Charts from Tree or Link Queries:
   • Exam Trap: A question asks why an engineer cannot select a newly created query in the Chart for Work Items widget configuration.
   • Answer: The query was saved as a Direct Links or Tree query. Only Flat List queries support chart creation.
2. Using the = Operator Instead of UNDER for Paths:
   • Exam Trap: Writing [System.AreaPath] = 'ECommerce' when trying to capture sub-teams.
   • Answer: Equality (=) matches only the exact root path. To include all nested child area paths, you must use the UNDER operator.
3. Confusing MTBF with MTTR:
   • Exam Trap: Identifying MTBF as a measure of recovery speed.
   • Answer: MTTR is the recovery duration. MTBF is the operational runtime between failures, reflecting system resilience.