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:

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.

Advertisement

Tfs Build Log: Querying Build Log Data

If you have ever wanted to programmatically parse past or current Tfs Build log data, then this post is for you.

 

Background

My company uses Tfs to implement a Rolling CI Build which often involves multiple Changesets per build. For the most part this is smooth sailing, however when the build breaks we want to to quickly be able to pinpoint the source and notify the guilty party. With multiple Associated Changesets per build, with multiple files checked-in by multiple developers, how to we efficiently pin-point to source of the break. We also do not want to parse the MSBuild log files since we are not creating log files for our Rolling CI Builds. Our solution then become querying and parsing the xml build information from the Project Collection SQL Database.

All the build log information is available on the Project Collection Database where the data is recorded one activity at a time so we are able to query all the way up to the current build activity. All this data can be found in the SQL Database, appropriately named Tfs_YourTeamProjectCollection, table Tbl_BuildInformation; where YourTeamProjectCollection is the name of your Team Project Collection. The build activity will appear as independent XML nodes, divided up into 16 different types.

 

Examining Tbl_BuildInformation

As mentioned above the build information will be located the Tbl_BuildInformation table and the build activity will be appear of 16 different types of XML nodes. Firstly let’s take a look at the the available columns:

and a query result for a single build:

 

Here is what you need to know about the columns:

• BuildId: Should be self explanatory it is typically the last octet of the IBuildDetail.BuildNumber (BuildDefinitionName.1.0.0.12345) or the Uri Fragment in the IBuildDetail.BuildUri (vstfs:///Build/Build/12345.)[1][2]
• NodeId: Id given to the Node on that specific node
• ParentId: This is the hierarchy that in the build log, this id represents it’s parent NodeId
• NodeType: Identify what type of node the row is (1 to 16)
• LastModifiedDate: Self explanatory
• Fields: Contains the XML data that we are interested in.

 

Understanding NodeTypes

Even though there are 16 different NodeTypes I am interested in only 5 of them. I had some difficulties finding documentation on the NodeTypes, so here is are examples of the XML Fields Column of each the NodeTypes that I use. The structure of all the NodeTypes are the same, and any value can be retrieved with the the XPath string “/Fields/Field[@Name=’AttributeValueOfInterest’][Value]”. For the sake of simplicity, I will only be referencing the ‘AttributeValueOfInterest’ from here on out.

NodeType 4: Project or Solution Compilation Results

This NodeType contains the MSBuild results for a Project or Solution component of your Build Targets. It provides the number of build errors or warnings for specific build. The Build Agent Local Path and the Server Path of the Project or Solution being built. Expect to see one entry for each Project or Solution Built.

Example Fields Column XML:

<Fields>
  <Field Name="CompilationErrors">
    <Value>0</Value>
  </Field>
  <Field Name="CompilationWarnings">
    <Value>0</Value>
  </Field>
  <Field Name="FinishTime">
    <Value>2013-10-30T20:01:00.3073686Z</Value>
  </Field>
  <Field Name="Flavor">
    <Value>Release</Value>
  </Field>
  <Field Name="LocalPath">
    <Value>C:\Builds\57\Path\To\Your\Project\Or\Solution.csproj</Value>
  </Field>
  <Field Name="Platform">
    <Value>AnyCPU</Value>
  </Field>
  <Field Name="ServerPath">
    <Value>$/Path/To/Your/Project/Or/Solution.csproj<Value>
  </Field>
  <Field Name="StartTime">
    <Value>2013-10-30T20:00:32.4797462Z</Value>
  </Field>
  <Field Name="StaticAnalysisErrors">
    <Value>0</Value>
  </Field>
  <Field Name="StaticAnalysisWarnings">
    <Value>0</Value>
  </Field>
  <Field Name="TargetNames">
    <Value />
  </Field>
</Fields>

NodeType 6: Total Compilation Results

This NodeType contains the MSBuild results for all components of your Build Targets. It provides the number of build errors or warnings for specific build.

Example Fields Column XML:

<Fields>
  <Field Name="Platform">
    <Value>Any CPU</Value>
  </Field>
  <Field Name="Flavor">
    <Value>Release</Value>
  </Field>
  <Field Name="TotalCompilationErrors">
    <Value>16</Value>
  </Field>
  <Field Name="TotalCompilationWarnings">
    <Value>9</Value>
  </Field>
  <Field Name="TotalStaticAnalysisErrors">
    <Value>0</Value>
  </Field>
  <Field Name="TotalStaticAnalysisWarnings">
    <Value>0</Value>
  </Field>
</Fields>

NodeType 7: Associated Changeset

This NodeType contains contains the information of an individual Changeset from the list of Associated Changesets. It contains the ChangsetId, who checked it in, and the the check-in comments.

Example Fields Column XML:

<Fields>
  <Field Name="ChangesetId">
    <Value>12345</Value>
  </Field>
  <Field Name="ChangesetUri">
    <Value>vstfs:///VersionControl/Changeset/12345</Value>
  </Field>
  <Field Name="CheckedInBy">
    <Value>Paul Selles</Value>
  </Field>
  <Field Name="Comment">
    <Value>Check-in comments.</Value>
  </Field>
</Fields>

NodeType 8: Build Log Errors

This NodeType contains contains any errors that are created during any build activity. These can be anything from compilation errors, network errors, to user created errors through the WriteBuildError class in the build process template[3]. As an example I will post the Fields Column XML for a compilation error. It is important to note that for error types other than compilation error, the AttributeValueOfInterest File, ServerPath, LineNumber, EndLineNumber are not present.

Example Fields Column XML:

<Fields>
  <Field Name="Code">
    <Value>BC30002</Value>
  </Field>
  <Field Name="EndLineNumber">
    <Value>270</Value>
  </Field>
  <Field Name="ErrorType">
    <Value>Compilation</Value>
  </Field>
  <Field Name="File">
    <Value>C:\Builds\57\Path\To\Your\Project\Or\CodeFile.cs</Value>
  </Field>
  <Field Name="LineNumber">
    <Value>270</Value>
  </Field>
  <Field Name="Message">
    <Value>Type 'EmailSet' is not defined.</Value>
  </Field>
  <Field Name="ServerPath">
    <Value>$/RQ/Path/To/Your/Project/Or/CodeFile.cs;C51077</Value>
  </Field>
  <Field Name="Timestamp">
    <Value>2013-10-30T20:10:34.6821707Z</Value>
  </Field>
</Fields>

NodeType 10: Build Log Warnings

This NodeType contains any warnings that are created during any build activity. This is very similar to NodeType 8, build activity errors. The build process template class for user created warning is WriteBuildWarning [3]. And as for the errors above, the posted example of the Fields Column XML for a compilation error. It is important to note that for warnings types other than compilation warnings , the AttributeValueOfInterest File, ServerPath, LineNumber, EndLineNumber are not present.

Example Fields Column XML:

<Fields>
  <Field Name="CompilationErrors">
    <Value>0</Value>
  </Field>
  <Field Name="CompilationWarnings">
    <Value>0</Value>
  </Field>
  <Field Name="FinishTime">
    <Value>2013-10-30T20:01:00.3073686Z</Value>
  </Field>
  <Field Name="Flavor">
    <Value>Release</Value>
  </Field>
  <Field Name="LocalPath">
    <Value>C:\Builds\57\Path\To\Your\Project\Or\Solution.csproj</Value>
  </Field>
  <Field Name="Platform">
    <Value>AnyCPU</Value>
  </Field>
  <Field Name="ServerPath">
    <Value>$/Path/To/Your/Project/Or/Solution.csproj<Value>
  </Field>
  <Field Name="StartTime">
    <Value>2013-10-30T20:00:32.4797462Z</Value>
  </Field>
  <Field Name="StaticAnalysisErrors">
    <Value>0</Value>
  </Field>
  <Field Name="StaticAnalysisWarnings">
    <Value>0</Value>
  </Field>
  <Field Name="TargetNames">
    <Value />
  </Field>
</Fields>

 

Querying The Database

In order to get these values, we will query this database with the current BuildId. As mentioned above, this is typically be the last octet of the IBuildDetail.BuildNumber (BuildDefinitionName.1.0.0.12345) or the Uri Fragment in the IBuildDetail.BuildUri (vstfs:///Build/Build/12345.)[1][2] Best practice is to confirm the BuildId in the table tbl_Build using the above mentioned number, 12345, in the BuildUri column. Once we have confirmed that we have to correct BuildId, we can go ahead and get the relevant build information with the following query:

SELECT * FROM [Tfs_YourTeamProjectCollection].[dbo].[Tbl_BuildInformation] WHERE [BuildId]=BuildId AND [NodeType] IN (4,6,7,8,10)

 

References

[1] IBuildDetail.BuildNumber Property. MSDN Library

[2] IBuildDetail.Uri Property. MSDN Library

[3] WriteBuildError. MSDN Library

[4] WriteBuildWarning. MSDN Libary