Paul Selles

Computers and cats

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:

dbo.tbl_BuildInformation_Columns

and a query result for a single build:

tlb_BuildInformation_query 

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

Advertisements

One response to “Tfs Build Log: Querying Build Log Data

  1. Pingback: Tfs Build: Query Build Table for historical Build Status, Compilation Status, and Test Status | Paul Selles

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: