English

Requisites

• SQL Server
• .Net Core 3.1+ Application
• Azure DevOps Account

Installation and Configuration

Package installation

PM > Install-Package AzureDevOpsTracker -Version 1.0.1

Step 1 - Configuring the Startup.cs

Once the nuget is installed, update the Startup.cs OWIN file of your .net application with the code ahead:

public void ConfigureServices(IServiceCollection services)
{
    services.AddAzureDevopsStateTracker(new AzureDevopsStateTracker.Data.DataBaseConfig("[CONNECTION_STRING]"));
}

public void Configure(IApplicationBuilder app, IServiceProvider serviceProvider)
{
    app.UseAzureDevopsStateTracker(serviceProvider);
}

Step 2 - Creating the Controller

For the Azure DevOps Service Hook Integration, it is necessary that your application has/have two EndPoints to receive a POST HTTP Request with a specific Json. These endpoints will receive Workitems data when it is created or updated.

public class ExampleController : Controller
{
    private readonly IAzureDevopsTrackerService _azureDevopsTrackerService;

    public ExampleController(
        IAzureDevopsTrackerService azureDevopsTrackerService)
    {
        _azureDevopsTrackerService = azureDevopsTrackerService;
    }

    [HttpPost("workitem-updated")]
    public async Task<IActionResult> WorkItemUpdated([FromBody] UpdatedWorkItemDTO updatedWorkItemDTO)
    {
        await _azureDevopsTrackerService.Update(updatedWorkItemDTO);
        return Ok();
    }

    [HttpPost("workitem-created")]
    public async Task<IActionResult> WorkItemCreated([FromBody] CreateWorkItemDTO createWorkItemDTO)
    {
        await _azureDevopsTrackerService.Create(createWorkItemDTO);
        return Ok();
    }
}

For more informations and an example about the Azure DevOps Tracker in a Azure Function, visit our repository Azure DevOps Function Tracker.

Configuring the Azure DevOps

Creating the subscription

Configurando serviço

The same steps has to be done to “WorkItem Updated”.

That’s it! Now all changes made to WorkItems will be automatically saved in the database!! 🎉😍

Examples

Time by State in a Sprint:

select 
	t.State, 
	dbo.ToTime(AVG(t.TotalWorkedTime)) as AvgTime,
	dbo.ToTime(MAX(t.TotalWorkedTime)) as MaxTime,
	dbo.ToTime(MIN(t.TotalWorkedTime)) as MinorTime,
	dbo.ToTime(SUM(t.TotalWorkedTime)) as SumTime,
	AVG(t.TotalWorkedTime / 60 / 60) as AvgInHours
from WorkItems w
join TimeByStates t on t.WorkItemId = w.Id
where 1=1 
and w.IterationPath = 'Sprint 12'
and w.Type IN ('Bug', 'Task')
group by t.State

Longest Tasks and Bugs by time at Active State in a Sprint:

select 
	w.Id, 
	w.Title,
	dbo.ToTime(t.TotalWorkedTime) as TotalWorkedTime, 
	w.AssignedTo,
	t.TotalWorkedTime
from WorkItems w
join TimeByStates t on t.WorkItemId = w.Id
where w.IterationPath = 'Sprint 12'
and w.Type IN ('Bug', 'Task')
and t.TotalWorkedTime > 60
and t.State = 'Active'
order by t.TotalWorkedTime desc

Live Demo

We have a DataBase with a public user to help you test the Azure DevOps Tracker in fully working!

• Server: tcp:sql-azure-devops-tracker.database.windows.net,1433
• Database: sqldb-azure-devops-tracker
• User: public_user
• Password: @Adt123456_

Azure DevOps project link that is connected to this database: Azure Devops - TypingHard

Work in Progress

• Pack 1
• Pack 2
• Pack 3