Query Work Items

Parameters

Output Columns

Input Columns

Examples

SSIS

Use Azure DevOps Connector in API Source component to read data or in API Destination component to read/write data:

Read from WorkItems table using API Destination

This Endpoint belongs to WorkItems table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

Azure DevOps

WorkItems

Select

ODBC application

Use these SQL queries in your ODBC application data source:

Query work items

SELECT * FROM query_workitems
WITH
(
    "Project" = 'abcd-1234-project',
    "Query" = 'SELECT * FROM WorkItems Order By [System.Id] DESC'
)

Get work items for the default project(specified by connection settings)

SELECT * FROM WorkItems

Get work items for the specific project

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

Create a query to get work items by the Id of the work items in the default project for the organization

To learn more about Query syntax refer to this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops and this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops

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
	')

Create a query to get work items by the Id of the work items in the specified project for the organization

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

Create a query to get work items by the types of work items in the default project for the organization

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

Query work items using Wiql

This example shows how to use WIQL language to query Azure DevOps WorkItems. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops

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

Query Sprint Hours by Team Member - Esimated vs Actual Completed

This query shows how to query total time spent by team members during a specific Sprint (i.e. Iteration Path). This query only works if your team is updating Original Estimate and Completed Hours on work item after they are done with the task.

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)

By default Azure DevOps API returns max 20000 items. If you want more than 20000 items then you can try below technique. Run enire query at once. Basically it creates many temp table each consisting 20000 rows each and then in final query we do UNION ALL

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:

Query work items

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM query_workitems
WITH
(
    "Project" = ''abcd-1234-project'',
    "Query" = ''SELECT * FROM WorkItems Order By [System.Id] DESC''
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get work items for the default project(specified by connection settings)

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

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get work items for the specific project

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

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Create a query to get work items by the Id of the work items in the default project for the organization

To learn more about Query syntax refer to this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops and this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops

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];

Create a query to get work items by the Id of the work items in the specified project for the organization

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];

Create a query to get work items by the types of work items in the default project for the organization

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

This example shows how to use WIQL language to query Azure DevOps WorkItems. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT 
	  Id
	, Title
	, Description
	, CreatedDate
	, Url
FROM WorkItems 
--WHERE Id=5283490 -- get just one comment for specific WorkItem Id
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 - Esimated vs Actual Completed

This query shows how to query total time spent by team members during a specific Sprint (i.e. Iteration Path). This query only works if your team is updating Original Estimate and Completed Hours on work item after they are done with the task.

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)

By default Azure DevOps API returns max 20000 items. If you want more than 20000 items then you can try below technique. Run enire query at once. Basically it creates many temp table each consisting 20000 rows each and then in final query we do UNION ALL

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];