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

Work in Progress

• Pack 1
• Pack 2
• Pack 3