Paul Selles

Computers and cats

Tfs Build: Query Build Table for historical Build Status, Compilation Status, and Test Status

If you are having trouble finding historical build data using the TFS API, build information can be found in your Team Foundation Server Databases [1]. The table Tbl_Build keeps a log of all your historical builds including, build number, start and stop times, controller id, drop location, and statuses. This can be and extremely powerful tool to look up Build Id values to look up historic build logs which contain detailed reports (as covered in Tfs Build Log: Querying Build Log Data).

You can find the Tbl_Build table within your Collection database (Tfs_YourTeamProjectCollection, where YourTeamProjectCollection is the name of your Team Project Collection.) The Status columns that we are interested in are BuildStatus, CompilationStatus, and TestStatus as seen in the SQl Server Management Studio screen capture below:

Tbl_Build_Columns

The BuildStatus column values map to the Microsoft.TeamFoundation.Build.Client BuildStatus Enumeration [2]. Here is the Database specific table:

BuildStatus Description
1 InProgress
2 Succeeded
4 PartiallySucceeded
8 Failed
16 Stopped

The CompliationStatus and TestStatus columns are trickier to deal with than BuildStatus column. Where the BuildStatus is pretty well self explanatory, both the CompliationStatus and TestStatus are heavily based on user definitions within the Build Process Template. That is to say that a Complitation/Test failure may not mean what you think that it means out of the box. Please review your Build Process Template and see how the CompliationStatus and TestStatus are set.

Moving forward, both CompliationStatus and TestStatus columns map to the Microsoft.TeamFoundation.Build.Client BuildPhaseStatus Enumeration [3]. Here are the Database specific table:

CompliationStatus Description
1 Failed
2 Succeeded
NULL Unknown
TestStatus Description
1 Failed
2 Succeeded
NULL Unknown

We can now query the database for historical build information, for example we can look for all failed builds between Jan 5th and Jan 18th of this year:

SELECT * FROM [Tfs_YouTeamProjectCollection].[dbo].[Tbl_Build] 
WHERE [BuildStatus]=8 
AND [StartTime] BETWEEN '1/5/2013 12:00:00 AM' 
	AND '1/18/2013 12:00:00 AM'

Be sure to check this out my previous post Tfs Build Log: Querying Build Log Data to learn how to programmatically read historical build data.

References

[1] Team Foundation Server Databases. MSDN Library.

[2] BuildStatus Enumeration. MSDN Libary.

[3] BuildPhaseStatus Enumeration. MSDN Libary.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: