Study guide
2.3 Work Item Queries, WIQL Charts & Analytics
Microsoft Certified: Azure DevOps Engineer Expert AZ-400
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Flow of Work, Traceability & Work Tracking
1.1 Flow of Work, GitHub Flow & Feedback Cycles 1.2 Work Tracking with Azure Boards & GitHub Projects 1.3 End-to-End DevOps Traceability & Quality Tracking
2 Chapter 2: DevOps Metrics, Dashboards & Team Collaboration
2.1 DORA Metrics, Flow Metrics & Dashboard Design 2.2 Lifecycle Metrics Across the Six DevOps Phases 2.3 Work Item Queries, WIQL Charts & Analytics 2.4 Project Documentation: Wikis, Mermaid Diagrams & Release Notes 2.5 Webhooks, Service Hooks & Microsoft Teams ChatOps
3 Chapter 3: Git Branching Strategies & Pull Request Workflows
3.1 Git Branching Models & Strategy Design 3.2 Pull Request Workflows & Merge Strategies 3.3 Branch Policies, Quality Gates & Compliance Restrictions
4 Chapter 4: Repository Management, Optimization & Git Data Operations
4.1 Git LFS Architecture & .gitattributes Tracking 4.2 LFS History Migration, git-fat & File Locking 4.3 Git Repository Scaling, Scalar & Monorepo Governance 4.4 Repository Permissions & Git Tag Governance 4.5 Git Data Recovery & Sensitive-Data Scrubbing
5 Chapter 5: Package Management & Dependency Versioning
5.1 Selecting Package Tools: Azure Artifacts vs. GitHub Packages 5.2 Feed Scoping, Upstream Sources & Pipeline Authentication 5.3 Package Views, Immutability & Quality Promotion 5.4 Dependency Versioning: SemVer, CalVer & Automated Versioning 5.5 Pipeline Artifact Versioning & Artifact Storage Strategy
6 Chapter 6: Pipeline Infrastructure, Agents & Integration
6.1 Selecting a CI/CD Solution: Azure Pipelines vs. GitHub Actions 6.2 GitHub-Azure Pipelines Integration & Hybrid Migration 6.3 Hosted vs. Self-Hosted Agents & Runners 6.4 VMSS Elastic Pools, Container Jobs & Agent Governance 6.5 Pipeline Triggers, Filters & Concurrency Management
7 Chapter 7: YAML Pipeline Architecture & Reusable Elements
7.1 YAML Pipeline Anatomy: Stages, Jobs, Steps & Workspaces 7.2 Job Execution Order: DAG Dependencies, Parallelism & Conditions 7.3 Reusable Elements: Templates, Parameters & Variables 7.4 Variable Groups, Secret Variables & Key Vault Linking 7.5 YAML Environments, Checks & Deployment Approvals
8 Chapter 8: Testing Strategy, Quality Gates & Code Coverage
8.1 Test Strategy Design: Local, Unit & Integration Testing 8.2 Load, Stress & Performance Testing in Pipelines 8.3 Test Execution Tasks, Agents & Results Integration 8.4 Code Coverage Analysis & SonarQube Integration 8.5 Quality Gates, Release Gates & Governance Checks
9 Chapter 9: Modern Deployment Strategies & Zero-Downtime Delivery
9.1 Progressive Delivery: Blue-Green, Canary & Ring Deployments 9.2 Minimizing Downtime: Deployment Slots & Rolling Updates 9.3 Feature Flags with Azure App Configuration & Feature Manager 9.4 Designing the High-Priority Hotfix Path 9.5 Ordered Dependency Deployments, Resiliency & Rollback
10 Chapter 10: Artifact Deployment & Database DevOps
10.1 Containerized Application Delivery: ACR, AKS & Container Apps 10.2 Binary, Web App, Function & Scripted Deployments 10.3 Database DevOps: State-Based & Migration-Based Strategies
11 Chapter 11: Infrastructure as Code (IaC) & Azure Deployment Environments
11.1 Infrastructure as Code Strategy: Bicep, ARM & Terraform 11.2 Desired State Configuration & Azure Machine Configuration 11.3 Azure Deployment Environments (ADE) & Developer Self-Service
12 Chapter 12: Pipeline Maintenance, Health & Modernization
12.1 Pipeline Health, Flaky Tests & Reliability Engineering 12.2 Pipeline Optimization: Caching, Slicing & Concurrency 12.3 Artifact & Build Retention Governance 12.4 Modernizing CI/CD: Classic to YAML Migration
13 Chapter 13: Authentication, Authorization & Identity Governance
13.1 Microsoft Entra Service Principals & Managed Identities 13.2 Service Connections, Workload Identity & Personal Access Tokens 13.3 Azure DevOps Projects, Teams & Permission Evaluation 13.4 Access Levels, GitHub Roles & Outside Collaborators
14 Chapter 14: Secrets Management, Sensitive Data & DevSecOps Scanning
14.1 Azure Key Vault, Secret Variables & Secretless CI/CD 14.2 Security Scanning Strategy, GHAS & Secret Scanning 14.3 CodeQL SAST in Pipelines and Containers 14.4 Software Supply Chain, Container Scanning & Cloud Defender
15 Chapter 15: Telemetry Collection, Azure Monitor & Alerting
15.1 Azure Monitor Architecture, AMA & Data Collection Rules 15.2 Application Insights & Specialized Azure Monitor Insights 15.3 Alerting Rules, Action Groups & GitHub Repository Insights
16 Chapter 16: Telemetry Analysis, Distributed Tracing & SRE Practices
16.1 Distributed Tracing, Application Map & Exception Triage 16.2 KQL Log Analytics, SRE Observability & Error Budgets
2.3 Work Item Queries, WIQL Charts & Analytics
Key Takeaways Explain This Topic AI
WIQL supports three query modes: flat list, work items and direct links, and tree of work items; widget types are restricted by the mode a query uses.
A dashboard widget can only bind to a query saved under Shared Queries - queries kept under My Queries are invisible to the rest of the team.
The 'Was Ever' operator is the only way to match a field's historical value, such as every user story ever assigned to a given engineer.
Analytics and its OData endpoints, not WIQL, supply revision history, which is why cumulative flow, velocity, lead time and cycle time widgets require Analytics.
The @CurrentIteration macro resolves against a team context, so a shared query must pass the team explicitly or it returns the wrong sprint for other teams.
Last updated: September 2026
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)
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.
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.
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:
- Macro Resolves to Typical use
- @Me The identity running the query Personal "my active bugs" tiles
- @Today Current date; supports arithmetic ( @Today - 14 ) Aging and stale-work queries
- @CurrentIteration The team's active sprint Sprint burndown and commitment tiles
- @CurrentIteration +/- n Adjacent sprints Next-sprint readiness checks
- @TeamAreas Area paths owned by a named team Multi-team portfolio rollups
- @Project Current project Templated queries shared across projects
@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
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.
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.
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.