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: Be very careful! 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’.

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.

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

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

Click Finish.

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

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:

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):

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:

(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:

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’.

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

 

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.)

Comments

• Anonymous
  June 11, 2014
  Hi, I am using TFS 2013, this article is obviously in relation to an earlier version of TFS. How can I view TFS usage on 2013? Thanks.

• Anonymous
  June 12, 2014
  @TFS User - This should still basically apply to TFS 2013 - the only difference being that instead of selecting a TfsActivityLogging database, you'll select the database for the project collection you want (i.e. TFS_DefaultCollection).

• Anonymous
  July 08, 2014
  Thanks Steven. Its Suberb :)

• Anonymous
  May 12, 2015
  Can I get the project activity instead of user activity?