Month: May 2018

I solved task how to create list of developers, which checkins causing failed builds in Team Foundation Server. I decided to solve this task using T-SQL query which is executed on team project collection database directly. Query uses common table expressions and window functions:

WITH BuildAssociatedChangeset AS
(
    SELECT 
        BuildId, 
        Fields.value('(/Fields/Field)[1]', 'int') AS ChangesetId, 
        Fields.value('(/Fields/Field)[3]', 'nvarchar(50)') AS CheckedInBy
    FROM 
        tbl_BuildInformation2 WITH (NOLOCK) 
    WHERE 
        NodeType = 12
),
BuildAssociatedChangesetWithRowNumber AS 
(
    SELECT
        BuildId,
        ChangesetId,
        CheckedInBy,
        ROW_NUMBER() OVER (PARTITION BY BuildId ORDER BY ChangesetId DESC) AS RowNumber
    FROM 
        BuildAssociatedChangeset
),
BuildTrigerringChangeset AS
(
    SELECT
        BuildId,
        ChangesetId,
        CheckedInBy,
        RowNumber
    FROM
        BuildAssociatedChangesetWithRowNumber 
    WHERE
        RowNumber = 1
)
SELECT
    ch.CheckedInBy,
    COUNT(b.BuildId) AS FailedBuilds
FROM 
    tbl_Build b WITH (NOLOCK)
JOIN
    BuildTrigerringChangeset ch ON b.BuildId = ch.BuildId
WHERE
    b.BuildStatus = 8 AND 
    b.StartTime > '2017-05-01'
GROUP BY
    ch.CheckedInBy
ORDER BY
    FailedBuilds DESC

Query was tested on TFS 2013.