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]
          ,  (
                    ‘”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”’ 
                        inserted i
            , (
                        dbo.[ADObjects] ao
                        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 ( who helped me clean up the SQL to be more readable and for catching errors in my rusty tSQL .


Leave a Reply

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

You are commenting using your 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