Automatically Created TFS Alerts

In TFS 2010 I have a basic set of alerts that I want created on every project.  This becomes tedious and hard to manage on a larger scale where multiple people have rights to create projects and there are a bunch of project starts and stops to consider. 

What you’ll need:

  • TFS 2010 Power Tools installed
  • Name of the Collection Database you want to create alerts for: CollectionDatabase
  • Name of the Account you want to be the owner of the alerts: Domain\UserName
  • Type of the alert: 0 – HTML, 1 – PlainText, 2 – SOAP Connection
  • Sample Alert Criteria from the Alert Explorer
    ‘WorkItemChangedEvent’,   ‘PortfolioProject = ‘[MyProject]’ AND “CoreFields/StringFields/Field[ReferenceName=’System.WorkItemType’]/NewValue” <> ‘Shared Steps|Status Report|Sprint Retrospective’

So to do this, we are going to be building a trigger on the TFS 2010 database that will create our alerts whenever a new project is created.  Modification of the TFS databases isn’t really supported, but this is an addition rather than a change and project creation is a relatively infrequent action so the impact is minimal.

You should be aware of a few tables for this:

  • [ADObjects] This is the table where information about the valid logons is stored, mapping AD SIDs to GUIDs that are internally used.  Every alert needs an owner and the account you select will be the only one able to see the alerts created this way.
  • [tbl_EventSubscription] There is one copy of this table in the tfs_Configuration database and one in each of the individual Collection databases.  Global alerts belong in the tfs_Configuration databases while ones that are project specific should be limited to the individual collection.  I prefer to create alerts at the individual level so they can be more granularly managed, but this is largely a matter of choice.
  • [tbl_Project] This database contains the list of TFS projects that have been created and the matching status of each. There is a seperate table called [tbl_Projects] that could be used for this, but it is updated more frequently with last_update and I don’t really want anything that might cause future triggers to be fired more often than needed.  Further, [tbl_Project] contains a status field, IsDeleted, where we could put an update trigger to delete matching alerts just to clean the database up, however, that is outside the scope of this post.

 So, for this example, I am going to create a SOAP type alert that connects all new projects to the SFTS: Scrum for Team System aggregation service so that aggregation rollups work.  The same steps can be used for email alerts or connection to other custom web services.

First we need to parse out the alert criteria that we copied out of the Alerts Explorer (this is part of the TFS Power Tools) so that we get the SQL escape characters.  In this case, I am telling the service to post the alert for all workitem changes other than Shared Steps, Status Reports, and Sprint Retrospectives.

This gives us the SQL Trigger Create Statement of:

CREATE TRIGGER CreateRollupSubscriptions
ON [tbl_Project]
AFTER INSERT
AS
     INSERT INTO
          dbo.[tbl_EventSubscription]
     VALUES
          (‘WorkItemChangedEvent’
          ,  (
               SELECT
                    ‘”PortfolioProject” = ”’
                    + i.ProjectName           — This lets us reference only the newly created project
                    + ”’ AND “CoreFields/StringFields/Field[ReferenceName=”System.WorkItemType”]/NewValue” ‘
                    + ‘<> ”Shared Steps|Status Report|Sprint Retrospective”’ 
                  FROM
                        inserted i
                )
            , (
                  SELECT
                        ao.[TeamFoundationId] 
                  FROM
                        dbo.[ADObjects] ao
                  WHERE
                        ao.SamAccountName = ‘UserName’)   — This sets the owner of the Alert
            , 0  — Sets the Schedule to be run immediately
            , 2  — Sets the Alert Type to 2 – SOAP Connection
            , http://%5BServerURL%5D:8080/ScrumforTeamSystem/3.0/WorkItemChangedEndPoint.asmx’  — Address or target of the alert
            , ‘TFS Autogenerated Rollups’   — This is the name of the Alert we are creating
            );

Whew and we’re done!  Now any time a new project is created, we will automatically have a new notification made.  Of course, you can create other SQL statements to generate a bunch of alerts, take other actions, or email you that a project was created.  How cool is that.

Special thanks to Aaron Lowe (http://www.aaronlowe.net) who helped me clean up the SQL to be more readable and for catching errors in my rusty tSQL .

Advertisements

TFS Service Authentication (TF30063)

I have some custom and third-party services (SFTS: Scrum for Team System) attached to my TFS 2010 server and began to get the following error in the event logs as the services failed.

Error: ‘TeamFoundationServerUnauthorizedException’
TF30063: You are not authorized to access http://%5BSERVER URL]:8080/tfs/[COLLECTION NAME].
     at Microsoft.TeamFoundation.Client.TfsConnection.ThrowAuthorizationException(Exception e)
     at Microsoft.TeamFoundation.Client.TfsConnection.UseCredentialsProviderOnFailure(Action action)
     at Conchango.TeamSystem.SubscribedEventHandler.Services.ServerConnectionService.ConnectToServer(String connectionString)

The effect that I was seeing was that the services appeared to be working just fine, but when they were attempting to write the results into work items, the server would stop the write claiming that the service did not have sufficient permissions to write to the system.  Since the service was impersonating an administrator on the TFS system, I knew this wasn’t the case, but still…  FAIL!

Following the steps in KB926642 got me through the issue allowing the server to reference itself using the FQDN and everything was working again.

http://support.microsoft.com/kb/926642

Method 1 (recommended): Create the Local Security Authority host names that can be referenced in an NTLM authentication request

// To do this, follow these steps for all the nodes on the client computer:

  1. Click Start, click Run, type regedit, and then click OK.
  2. Locate and then click the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
  3. Right-click MSV1_0, point to New, and then click Multi-String Value.
  4. In the Name column, type BackConnectionHostNames, and then press ENTER.
  5. Right-click BackConnectionHostNames, and then click Modify.
  6. In the Value data box, type the CNAME or the DNS alias, that is used for the local shares on the computer, and then click OK.

    Note Type each host name on a separate line.

    Note If the BackConnectionHostNames registry entry exists as a REG_DWORD type, you have to delete the BackConnectionHostNames registry entry.

  7. Exit Registry Editor, and then restart the computer.

Making a work item only editable by its creator in TFS 2010

I was recently confronted by someone building a TFS template for a client that had an interesting business rule they had to support.  They needed to be able to allow only the creator of the Work Item to edit or make changes to it once it was created.  There is a lot of magic you can do with the TFS rules and field comparisons, but this one was a bit harder than most.

When TFS uses a work item, there are actually three passes that are made through the fields putting the rules in an order of precedence (For the full explanation check out this article):

  1. The first pass sets and copies values we well as dealing with any conditional rules (WHEN and WHENNOT, etc.)
  2. The second pass evaluates any fields that were changed in the first pass and then evaluates the WHENCHANGED rules, releasing the work item for editing
  3. The last pass is on commit when the Work Item is saved to the database.  This catches any rules triggered during the editing process and finally the SERVERDEFAULT rules.

The Solution

We can take advantage of this ordering to get that locking functionality to work by creating new rules on System.ChangedBy

<FieldDefinition reportable=”dimension” refname=”System.ChangedBy” name=”Changed By” syncnamechanges=”true” type=”String”>
      <COPY from=”currentuser” />
      <FROZEN />
</FieldDefinition>

How it works

  1. Since Copy is processed before everything else, we can use it to force the value on load before any of the other rules are posted (like the frozen one).  Then we freeze the value (the rule doesn’t fire if the field is empty).
  2. If the user that created the work item goes to edit it again, they are allowed as the value it tries to copy is the same so it never commits to trigger the frozen constraint.
  3. If a different user tries to edit the work item, the value is copied over top, but then the frozen constraint is triggered as it does the validation pass before it releases the work item into edit mode generating the error.

The downside here is that there isn’t a nice error message or prompt telling the user that they don’t have the ability to edit a work item that they didn’t create.   While that would be truly awesome, we might be content with the fact that the business rule is maintained (perhaps that makes an auditor somewhere happy.)

As an extension of this, we can also take advantage of the <FROZEN/> field by specifying the groups that are affected (or not in this case) by the change.  Here we allow Collection Administrators the ability to override, but keep the rule for everyone else.  Neat!

<FieldDefinition reportable=”dimension” refname=”System.ChangedBy” name=”Changed By” syncnamechanges=”true” type=”String”>
      <COPY from=”currentuser” />
      <FROZEN not=”[Global]\Project Collection Administrators” />
</FieldDefinition>

Troubleshooting a Broken TFS Data Tier

I have seen a number of questions out there around troubleshooting a broken data tier in TFS 2010 in cases where the application tier appears to be working correctly, but the whole system is down because the AT can no longer reach the core databases.   Here are the steps I have gone through to resolve the issue and to get back into production.

Start at the Data Tier

  1. For now, let’s ignore anything that isn’t App Tier (AT) or Data Tier (DT) as almost all of that can be rebuilt or you probably have it elsewhere (though possibly not the case for SharePoint documents).
  2.  First, check your database backups and their logs to make sure you have them in a safe place.  I am hoping you went through the real backup procedure (either through the painful process with all the SQL statements or through the Oct version of the Power Tools) rather than just clicking “backup” on the databases.  Make a copy of them elsewhere in case the problem comes down to a bad disk or something with the hardware.  If you can have them in a safe place, first, that is some measure of security.
  3. Check access to the databases with SQL Management studio.  You need to make sure you are using the account that you expect TFS to be using for data layer access.  Try connecting to Tfs_Configuration and to your collections Tfs_[Collection Name]
  4. Triage issues in the event logs

Validate Network Connectivity

  1. Log on to the AT server
  2. Validate that you can ping the Data Tier by its server name and its full name (FQDN)
  3. Triage issues in the event log

Validate AT Connection

  1. Open the TFS Administration Console
  2. Check the TFS logs here
  3. Click on the Application Tier Node and scroll down in the main pane to confirm that the data connection is right
  4. At this point you can try resetting the database registration in TFS
    Attempt to repair the connection http://msdn.microsoft.com/en-us/library/ee349268.aspx
    Attempt to Re-register from scratch RegisterDbs  http://msdn.microsoft.com/en-us/library/ms252443.aspx
    RemapDbs (more complex/ split server scenarios) http://msdn.microsoft.com/en-us/library/ee349262.aspx
  5. If you are still not at the problem, I would go through the steps as though this were a data tier move…  that means you will try to reattach the working AT to the DT.  You should first try this with the existing databases, but these may have gotten munged so you’d then move to the backup.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;955601

If you didn’t have the right backups, you may have to recover the collections once connected, but this would result in possible data loss of the unsynched portions (usually only a few seconds of difference) http://msdn.microsoft.com/en-us/library/ff407077.aspx

The TFS 2010 Admin Tool is Finally Here!

Managing permissions can be frustrating.  Managing them in TFS 2010 can be a nightmare!  Permissions for each of the three tiers (TFS Application Tier, the Reporting Server, and the SharePoint Portal) each having different authorization management tools and settings makes configuration more painful than even the average masochist is willing to sign up for.  Even worse is when you are in a troubleshooting scenario with an end-user or client where things just don’t seem to be working correctly and you’re chasing down screwy permissions — Enter the TFS Admin Tool.

We all got spoiled using a pervious version of the tool in out 2005 and 2008 environments and it worked great until TFS 2010 came out.  Then it would only function if you had Visual Studio  2008 SP1 installed, leaving those of us who upgraded in the dust wanting for some way to wrangle our user permissions back under control.  I was a little worried about it working in my production scenario with SharePoint 2010 Enterprise in the mix as well as SSL encryption, but here it is and life is good again.

Special thanks to the hard work of  Ladislau Szomoru as well as the other contributors on CodePlex who have made my week.

You can download the TFS Admin Tool here: http://tfsadmin.codeplex.com/

Custom Email Templates in TFS 2010

Team Foundation Server 2010 can create custom alerts that fit almost any set of criteria you can come up with, but the emails that it generates are pretty generic, though informative.  Wouldn’t it be great if you were able to customize the information that was contained in them and maybe even spice them up a little bit with corporate branding?  With just a little bit of work, you can have these looking much better.

On your server that is housing the TFS 2010 Application Tier, all of the e-mail templates are contained in the same folder where TFS Job Agent was installed.  In my case, the files are located at:

C:\Program Files\Microsoft Team Foundation Server 2010\Application Tier\TFSJobAgent\Transforms

There are many different templates here for different kinds of alerts, but for this example, we’ll concentrate on the normal Work Item alerts.  These are the ones that are used for alerts relating to Work Item changes, reassignment, and status updates, but there are others there for build alerts, checkins and the like.  These two files are:

  • WorkItemChangedEvent.xsl — This is the HTML version of the alert that is usually sent when the alert is triggered.
  • WorkItemChangedEvent.plaintextXsl — This is the plaintext version of the alert. 

Opening these files, you will see pretty standard XSL used the transform the TFS data into the alert e-mail.  For this example, let’s just add a header and create a link to the style sheet that I use for my custom process guidance to make sure the branding is identical across all communication with the end-users of the TFS instance.

So let’s modify the header and body of the e-mail to make things a bit more friendly…

<?xml version=“1.0” encoding=“UTF-8”?>
<xsl:stylesheet xmlns:xsl=http://www.w3.org/1999/XSL/Transform&#8221; version=”1.0″>
   <xsl:import href=“TeamFoundation.xsl”/>
   <!– Common TeamSystem elements –>
   <xsl:template match=“WorkItemChangedEvent”>
      <head>
      <!– Pull in the command style settings –>
      <xsl:call-template name=“style”>
      </xsl:call-template>
      <!– Link to the Company TFS Style Sheet –>
      <link href=http://processguidance.Company.com/Style/Content.css&#8221; type=“text/css” rel=“stylesheet” />
   </head>
<body>
      <!– Company Email header –>
   <div>
       <img height=“17” src=http://processguidance.Company.com/Images/EmailHeaderBar.png&#8221; />
      <br />
      <div>
         <div style=“float:left;”>
            <img alt=“Company Name” height=“40” src=http://processguidance.Company.com/Images/Company_logo.gif&#8221; width=“270” />
         </div>
      </div>
   </div>
<!– End Company Header–>

Now that we have some really basic markup in the header of the e-mail, our correspondence with the end-user will look much better.  Of course, you can dig a bit deeper to change the fields displayed in the alerts or add a footer.  Just make sure that you always have a backup of the original file and test test test!

TFS 2010 Rules Limited to 255 Characters

Using HTML fields in your TFS 2010 Work Item definitions gives you great flexibility for user input or storage of presentable information from external sources (System Info from Test Manager, for instance).  This is great if you need to input a lot of information with formatted text, hyperlinks, or even links out to external resources.  This is leaps and bounds over the text fields in previous versions.  One area where this falls short as in the TFS 2010 rules that are attached to the fields.  Don’t get me wrong, these work consistently and make life so much easier, but there is a huge missed opportunity here.

Using default values for these fields, it is possible to create blank tables and formatted headers to prompt the end users of the work items for well-formed input.  This makes the user experience so much better and gives you a much better chance of getting the information you want on the form.  The downside comes in the formatting of the rules.  While the HTML and PlainText data types are stored as blobs in the database, the rules are still stored as 255 character strings.  This is a little bit inconvenient.  The only good part here is that the HTML rendering in VisualStudio isn’t that particular and allows you to make shortcuts in your markup to make the message as short as possible.  After pulling the whitespace out you can usually come up with something that is at least usable, though you’ll have to sacrifice all of the fun styling and formatting you might otherwise have put in there.

Maybe that’ll be a feature for the next release?