Querying the TFS Database to Check TFS Usage

Why would you want to know how many users are actually using Team Foundation Server?  Well, for starters:

  • You want to make sure that each user in your environment using TFS is properly licensed with a TFS CAL (Client Access License). 
  • You want to show management just how popular TFS is in your environment.
  • You want to request additional hardware for TFS, and want to show current usage capacity.

But, what if your users are spread out all over the world, so you can’t just send a simple email asking, “Hey, are you using TFS?”

One relatively straightforward way is to ask your TFS server’s database.  TFS logs activity in a database ‘TfsActivityLogging’, specifically in a table ‘tbl_Command’.

NOTE: It’s not supported to go directly against the database, so take note of 2 things:

  1. Be very careful!
  2. Be clear that this isn’t supported. This process works, but only in the absence of a supported way to query TFS usage. Just because I work for Microsoft, doesn’t mean you can get official support from MS on this.

All that out of the way, the simple way to do this is to use Excel:

Open Excel.

Go to the Data tab and select ‘From Other Sources’ in the ‘Get External Data’ group, and select ‘From SQL Server’.

image

The Data Connection Wizard will open.  Follow steps to connect to the SQL Server that’s used by TFS, selecting the ‘TfsActivityLogging’ database and the contained ‘tbl_Command’ table.

image

Enter the SQL Server name that TFS uses.  For the below, my SQL server is at ‘tfsrtm08’.

image

Select the ‘TfsActivityLogging’ database, then select the ‘tbl_Command’ table. Click Next.

image

Click Finish.

Select how you’d like to import the table’s data.  For this example, I’m choosing ‘PivotTable Report’.

image

Now you’re ready to get the data you want:

Listing All Users Who Have Touched TFS

In the ‘PivotTable Field List’ panel on the right, select the ‘IdentityName’ field.  Your spreadsheet should look something like this:

image

If you just want a list of users that have touched TFS, then you’re done (in my example, I really only have 2 accounts, and one is the TFSSERVICE account that actually runs TFS).

However, if you want a little extra information about your users’ activities, you can do a couple extra things.

List Users and Their Relative Activity Levels

Add the ‘ExecutionCount’ field to the ‘Values’ section of the PivotTable, and you’ll see the number of commands each user has run against TFS (some minor, like gets, and other major, like changing ACL’s):

image

List Users and Their Specific Activity Levels

Add first the ‘ExecutionCount’ field to the ‘Values’ section of the PivotTable, then add the ‘Command’ field to the ‘Row Labels’ section:

image

(Again, remember that some of these commands are less significant than others, but still indicate user activity.)

List Users and Their Clients

Add the ‘UserAgent’ field to the ‘Row Labels’ section of the PivotTable:

image

List Users and Their Last Activity Time

Add ‘IdentityName’ to the ‘Row Labels’ section of the PivotTable and ‘StartTime’ to the ‘Values’ section.  Then click ‘Count of StartTime’ (in the Values section) and select ‘Value Field Settings’.  Change the ‘Summarize the value field by’ value to ‘Max’.

image

Click ‘Number Format’ and set the format to ‘Date’.  Click OK.  You’ll now see the last activity date for each user.

image 

I hope this helps!

Other Tip:

  • You’ll probably see (like in my example) the built-in accounts and their activities (i.e. TFSSERVICE, perhaps TFSBUILD as well).  You may want to filter those ones out from your report.
  • I’ve heard conflicting reports about how much data the ‘tbl_Commands’ table retains (some say just the preceding week).  In my example, I queried the ‘Min’ start times for logged activities and went back over 5 months.  Just something to think about:  Your mileage may vary greatly.  (Apparently a clean-up job is supposed to run periodically which trims this table.)