Endpoint Query Work Items

Name

query_workitems

Description

Get work items associated with the specified project and organization that are filtered by a Wiql query. (A team can optionally be specified as well.). Read more about this API here https://learn.microsoft.com/en-us/rest/api/azure/devops/wit/wiql/query-by-wiql?view=azure-devops-rest-7.0 and here https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops [API reference]

Related Tables

WorkItems

Parameters

Output Columns

Input Columns

Examples

SSIS

Use Azure DevOps Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the WorkItems table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - Azure DevOps

Read and write Azure DevOps (Cloud or On-Premises) data effortlessly. Integrate, manage, and automate work items, projects, and teams — almost no coding required.

Azure DevOps

WorkItems

Required Parameters
Project Name	Fill-in the parameter...
Wiql Query	Fill-in the parameter...
Optional Parameters
Team Id or Name
Use time precision
Expand Parameters (Set Blank for fast response)	Relations

API Destination

This Endpoint belongs to the WorkItems table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to query work items:

API Destination - Azure DevOps

Read and write Azure DevOps (Cloud or On-Premises) data effortlessly. Integrate, manage, and automate work items, projects, and teams — almost no coding required.

Azure DevOps

WorkItems

Select

Required Parameters
Project Name	Fill-in the parameter...
Wiql Query	Fill-in the parameter...
Optional Parameters
Team Id or Name
Use time precision
Expand Parameters (Set Blank for fast response)	Relations

ODBC application

Use these SQL queries in your ODBC application data source:

List work items (default project)

<p>Returns work items from the default project specified in the connection (or the project you set in the WITH clause). Use this as the baseline list; you can filter by ID, state, or other columns in the WHERE clause, or pass a WIQL query in WITH to filter on the server.</p>

SELECT * FROM WorkItems

List work items for a project

<p>Lists work items for a specific project by supplying the project name in the WITH clause. Use this when your connection default is one project but you need to query another. You can combine with a WHERE clause or a WIQL query in WITH to narrow results.</p>

SELECT * FROM WorkItems WITH (Project='ProductTesting')

List work items with ParentId

<p>By default, relation fields such as ParentId are not returned so that list queries stay fast. When you need parent-child or other link information, set <code>Expand='Relations'</code> in the WITH clause. The result set will then include ParentId and other relation columns so you can join or filter by hierarchy.</p>

SELECT * FROM WorkItems WITH(Expand='Relations')

Query work items by WIQL (filters and date range)

<p>Use a WIQL (Work Item Query Language) query to filter work items by project, ID range, changed/created dates, work item type, state, priority, and more. The query runs on the server and returns only matching rows. This example shows typical filters: project, ID range, date variables like @Today and @StartOfYear, and ordering by changed date.</p><p>For full syntax and operators, see <a href="https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops">WIQL syntax</a> and <a href="https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops">query operators and variables</a>.</p>

SELECT * FROM WorkItems WITH (Query='SELECT * FROM WorkItems 
WHERE [System.Id] > 2000 AND [System.Id] < 7050 
	AND [System.TeamProject]=''ProductTesting''
	AND [System.ChangedDate] < @Today
	AND [System.CreatedDate] > @StartOfYear
	AND [System.WorkItemType]=''Task'' 
	AND [System.State]=''Resolved'' 
	AND [Microsoft.VSTS.Common.Priority] >= 1
	ORDER BY [System.ChangedDate] DESC
	')

Query work items modified after a date (dynamic)

<p>Use placeholder functions (e.g. monthstart, today, yearend) and arithmetic (e.g. monthstart-1d) to build dynamic dates in your WIQL query so the same statement always reflects the intended period without manual date changes. The placeholder is evaluated before the query is sent to Azure DevOps.</p><p>For placeholder syntax and options see <a href="https://zappysys.com/links/?id=10014">placeholder functions</a>. For WIQL see <a href="https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops">WIQL syntax</a>.</p>

SELECT * FROM WorkItems WITH (Query='SELECT * FROM WorkItems 
WHERE [System.TeamProject]=''ProductTesting''
	AND [System.ChangedDate] >= ''<<monthstart-1d,FUN_TO_DATE>>'' 
ORDER BY [System.ChangedDate] DESC
	')

Query work items by WIQL in a project

<p>Runs a WIQL query in a specific project by passing both the project name and the query in the WITH clause. Use this when the connection default is different from the project you want to query, or when you need to target one project explicitly. The query can filter by ID, state, type, or any other WIQL criteria.</p>

SELECT * FROM WorkItems WITH (Project='PosProject', Query='SELECT * FROM WorkItems WHERE [System.Id] = 2819')

Query work items by type (e.g. Task)

<p>Returns work items of a given type (e.g. Task, Bug, User Story) by filtering with a WIQL query on <code>System.WorkItemType</code>. Use this to list all tasks, all bugs, or any other type. You can combine with other WIQL conditions (state, project, dates) in the same query string.</p>

SELECT * FROM WorkItems WITH (Query='SELECT * FROM WorkItems WHERE [System.WorkItemType] = ''Task''')

Query work items using WIQL

<p>Run a WIQL query to filter work items by project and criteria; the query is passed in the WITH clause and executed on the server. You can select specific columns (e.g. Id, Title, Description, CreatedDate, Url) or use * to return all columns. WIQL supports project, ID, state, work item type, dates, and more.</p><p>For syntax and operators see <a href="https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops">WIQL syntax</a>.</p>

SELECT 
	  Id
	, Title
	, Description
	, CreatedDate
	, Url
FROM WorkItems 
--WHERE Id=5283490 -- uncomment to get just one WorkItem
WITH(Query='SELECT * FROM WorkItems WHERE [System.TeamProject]=''ProductTesting'' and [System.Id]=6455 ORDER BY [System.Id] DESC')

Query sprint hours by team member (estimated vs completed)

<p>Returns aggregated original estimate, remaining work, and completed work per team member for a sprint (iteration path). Useful for sprint burndown or capacity reporting. The query groups by iteration path and assignee; the WITH clause filters to a specific sprint and state (e.g. Resolved, Closed).</p><p>This only gives meaningful results if your team updates Original Estimate and Completed Work on work items as they progress. You can change the iteration path in the query to cover other sprints or the whole product.</p>

SELECT 
	  IterationPath as Sprint
	, AssignedToUniqueName as AssignedTo
	, SUM(OriginalEstimate) as OriginalHours_Total
	, SUM(RemainingWork) as RemainingWork_Total
	, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
	  Project='ProductTesting'
	  
	  --//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
	  ,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\Sprint 1'' and State IN(''Resolved'', ''Closed'')'
	  
	  --Use below to query all sprints for this Product
	  --,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\'' and State IN(''Resolved'', ''Closed'')'
)

Read more than 20000 work items (UNION ALL)

<p>The Azure DevOps API returns at most 20000 work items per request. To read more, run multiple queries that each request a different ID range (e.g. 1–19999, 20000–39999) and combine the results. This example uses temp tables and UNION ALL so you get one result set; you can add more ranges by adding more SELECT INTO and UNION ALL lines.</p><p>Run the full script at once. Adjust the ID ranges and the number of temp tables to match your data size.</p>

SELECT * INTO #t1 FROM WorkItems WITH(Project='ProductTesting', Query='SELECT * FROM WorkItems WHERE [System.Id]>=1 and [System.Id]<20000');
SELECT * INTO #t2 FROM WorkItems WITH(Project='ProductTesting', Query='SELECT * FROM WorkItems WHERE [System.Id]>=20000 and [System.Id]<40000');
SELECT * INTO #t3 FROM WorkItems WITH(Project='ProductTesting', Query='SELECT * FROM WorkItems WHERE [System.Id]>=40000 and [System.Id]<60000');
--//....
--//add more temp tables above and update UNION ALL too

SELECT * FROM #t1 
UNION ALL 
SELECT * FROM #t2
UNION ALL 
SELECT * FROM #t3
;

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

List work items (default project)

<p>Returns work items from the default project specified in the connection (or the project you set in the WITH clause). Use this as the baseline list; you can filter by ID, state, or other columns in the WHERE clause, or pass a WIQL query in WITH to filter on the server.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

List work items for a project

<p>Lists work items for a specific project by supplying the project name in the WITH clause. Use this when your connection default is one project but you need to query another. You can combine with a WHERE clause or a WIQL query in WITH to narrow results.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Project=''ProductTesting'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

List work items with ParentId

<p>By default, relation fields such as ParentId are not returned so that list queries stay fast. When you need parent-child or other link information, set <code>Expand='Relations'</code> in the WITH clause. The result set will then include ParentId and other relation columns so you can join or filter by hierarchy.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH(Expand=''Relations'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query work items by WIQL (filters and date range)

<p>Use a WIQL (Work Item Query Language) query to filter work items by project, ID range, changed/created dates, work item type, state, priority, and more. The query runs on the server and returns only matching rows. This example shows typical filters: project, ID range, date variables like @Today and @StartOfYear, and ordering by changed date.</p><p>For full syntax and operators, see <a href="https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops">WIQL syntax</a> and <a href="https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops">query operators and variables</a>.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Query=''SELECT * FROM WorkItems 
WHERE [System.Id] > 2000 AND [System.Id] < 7050 
	AND [System.TeamProject]=''''ProductTesting''''
	AND [System.ChangedDate] < @Today
	AND [System.CreatedDate] > @StartOfYear
	AND [System.WorkItemType]=''''Task'''' 
	AND [System.State]=''''Resolved'''' 
	AND [Microsoft.VSTS.Common.Priority] >= 1
	ORDER BY [System.ChangedDate] DESC
	'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query work items modified after a date (dynamic)

<p>Use placeholder functions (e.g. monthstart, today, yearend) and arithmetic (e.g. monthstart-1d) to build dynamic dates in your WIQL query so the same statement always reflects the intended period without manual date changes. The placeholder is evaluated before the query is sent to Azure DevOps.</p><p>For placeholder syntax and options see <a href="https://zappysys.com/links/?id=10014">placeholder functions</a>. For WIQL see <a href="https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops">WIQL syntax</a>.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Query=''SELECT * FROM WorkItems 
WHERE [System.TeamProject]=''''ProductTesting''''
	AND [System.ChangedDate] >= ''''<<monthstart-1d,FUN_TO_DATE>>'''' 
ORDER BY [System.ChangedDate] DESC
	'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query work items by WIQL in a project

<p>Runs a WIQL query in a specific project by passing both the project name and the query in the WITH clause. Use this when the connection default is different from the project you want to query, or when you need to target one project explicitly. The query can filter by ID, state, type, or any other WIQL criteria.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Project=''PosProject'', Query=''SELECT * FROM WorkItems WHERE [System.Id] = 2819'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query work items by type (e.g. Task)

<p>Returns work items of a given type (e.g. Task, Bug, User Story) by filtering with a WIQL query on <code>System.WorkItemType</code>. Use this to list all tasks, all bugs, or any other type. You can combine with other WIQL conditions (state, project, dates) in the same query string.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Query=''SELECT * FROM WorkItems WHERE [System.WorkItemType] = ''''Task'''''')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query work items using WIQL

<p>Run a WIQL query to filter work items by project and criteria; the query is passed in the WITH clause and executed on the server. You can select specific columns (e.g. Id, Title, Description, CreatedDate, Url) or use * to return all columns. WIQL supports project, ID, state, work item type, dates, and more.</p><p>For syntax and operators see <a href="https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops">WIQL syntax</a>.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT 
	  Id
	, Title
	, Description
	, CreatedDate
	, Url
FROM WorkItems 
--WHERE Id=5283490 -- uncomment to get just one WorkItem
WITH(Query=''SELECT * FROM WorkItems WHERE [System.TeamProject]=''''ProductTesting'''' and [System.Id]=6455 ORDER BY [System.Id] DESC'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query sprint hours by team member (estimated vs completed)

<p>Returns aggregated original estimate, remaining work, and completed work per team member for a sprint (iteration path). Useful for sprint burndown or capacity reporting. The query groups by iteration path and assignee; the WITH clause filters to a specific sprint and state (e.g. Resolved, Closed).</p><p>This only gives meaningful results if your team updates Original Estimate and Completed Work on work items as they progress. You can change the iteration path in the query to cover other sprints or the whole product.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT 
	  IterationPath as Sprint
	, AssignedToUniqueName as AssignedTo
	, SUM(OriginalEstimate) as OriginalHours_Total
	, SUM(RemainingWork) as RemainingWork_Total
	, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
	  Project=''ProductTesting''
	  
	  --//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
	  ,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\Sprint 1'''' and State IN(''''Resolved'''', ''''Closed'''')''
	  
	  --Use below to query all sprints for this Product
	  --,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\'''' and State IN(''''Resolved'''', ''''Closed'''')''
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Read more than 20000 work items (UNION ALL)

<p>The Azure DevOps API returns at most 20000 work items per request. To read more, run multiple queries that each request a different ID range (e.g. 1–19999, 20000–39999) and combine the results. This example uses temp tables and UNION ALL so you get one result set; you can add more ranges by adding more SELECT INTO and UNION ALL lines.</p><p>Run the full script at once. Adjust the ID ranges and the number of temp tables to match your data size.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * INTO #t1 FROM WorkItems WITH(Project=''ProductTesting'', Query=''SELECT * FROM WorkItems WHERE [System.Id]>=1 and [System.Id]<20000'');
SELECT * INTO #t2 FROM WorkItems WITH(Project=''ProductTesting'', Query=''SELECT * FROM WorkItems WHERE [System.Id]>=20000 and [System.Id]<40000'');
SELECT * INTO #t3 FROM WorkItems WITH(Project=''ProductTesting'', Query=''SELECT * FROM WorkItems WHERE [System.Id]>=40000 and [System.Id]<60000'');
--//....
--//add more temp tables above and update UNION ALL too

SELECT * FROM #t1 
UNION ALL 
SELECT * FROM #t2
UNION ALL 
SELECT * FROM #t3
;';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];