snooping into TeamSystem activity logging

Brian Harry had blogged before on how we are using the activity logging on our Team System data-tier to actually measure load on our Team Foundation Server.

How to enable logging

TFSActivityLogging is a databsae created on the DT (data-tier) of TFS. To use it first you need to enable the logging. To do that locate the top level web.config file on the TFS. Its usually in some place like %ProgramFiles%\Microsoft Team Foundation 2005\Web Services\Web.config . Search for CommandLogging in the file and set it to something like Normal. After this you are set to go and all commands will be logged in the TFSActivityLogging database.

How to see the log

Open SQL Server Management Studio and run the simple SQL query select * from tbl_Command against this TFSActivityLogging database. You can also do this directly from the command prompt using T-SQL. Follow the steps below to do this

  • bring up a command prompt

  • run sqlcmd -S localhost -E

  • type select * from TFSActivityLogging.dbo.tbl_Command

What you can do with it

This log is used by us for various purpose including calculating load and also determining usage-pattern. If you have access to the DT you are next to God and you can also potentially snoop on other users. I'll take a simple example. Last time I had posted a Rss feed generator for Team Build. I just wanted to check if people are really using it. Since I know calls to my feed generator will result in calling the web-method GetListOfBuilds I ran the following command

select * from tbl_Command where tbl_Command.Command = 'GetListOfBuilds'

This gets me something like below

4994 TFS Build GetListOfBuilds 0 2005-12-20 10:11:24.920 2015309 ArthurDent Mozilla/4.0  NULL
5001 TFS Build GetListOfBuilds 0 2005-12-20 10:13:24.213 892921 Trillian Team Foundation (devenv.exe) NULL
5016 TFS Build GetListOfBuilds 0 2005-12-20 10:20:39.750 498415 Slartibartfast RssBandit/ 
5448 TFS Build GetListOfBuilds 0 2005-12-20 10:37:27.690 392972 Beeblebrox RSSOwl/1.2 2005-11-06 NULL

A look into this tells me that Slartibartfast and Beeblebrox did use Rss aggregator to get to the feed. ArthurDent used only web-UI (through a browser) and trillian only uses devenv to get the build details. I can even tell which aggregators are used by Slartibartfast (RssBandit) and Beeblebrox (RSSOwl) and they are in my good books for using my feed generator (you know who you are folks 🙂 ).

Comments (9)

  1. Ankit says:

    Do you really have to use characters from Hitchhikers guide in all your samples?? 🙂

    These admin guys are always snooping around!! Don’t teach them more tricks, better still hinder there actions. Now I’m ready for my admin to send me an email that I have a habit of checking out files and undoing checkout frequently 🙁

  2. Ok then what about

    select * from TFSActivityLogging.dbo.tbl_Command where IdentityName = ‘Ankit’

  3. We already saw how we could use the QueryServerRequests web method to tell the calls that are actively…

  4. Rob Caron says:

    James Manning shares some more PowerShell goodness in his post that describes how to watch Team…

  5. Una vez instalado Team Foundation Server y habiendo verificado la misma, veamos algunos aspectos iniciales…

  6. says:

    I have followed your tip to enable logs at top level web.config by setting Normal as value and even rebooted server though it was not required. None of the logs are shoowing up there.



  7. Harvin says:

    Do you know where what timestamp is stored in the StartTime field of the tblCommand?

    reason is that the StartTime recorded in my table is not the data tier system time, and nither is it the app tier system time.

    So where is this time captured?

  8. Sorry Harvin, it’s been 3 years since I worked on TFS and I don’t remember anymore..

  9. Sorry Harvin, it’s been 3 years since I worked on TFS and I don’t remember anymore..

Skip to main content