How to generate a report of active users who log onto the TFS server

  • Article

 

One of the usual questions people ask is how to keep track of people connecting to TFS. It’s mostly for auditing purposes.

Here is a simple query that you run against the collection database to find the same.

This query will give you the list of people who are connected to the collection and the last time they connected.

It also lists the last activity and also time taken for the execution.

Select IdentityName AS ID,

StartTime AS Last_Access_Time,

Command as Reason,

IPAddress as IP,

ExecutionTime as Time

from tbl_Command where CommandId in

(Select Max(CommandId) from tbl_Command where Application not like 'Team Foundation JobAgent' Group By IdentityName ) order by Last_Access_Time desc

Below is a nested query and you can do a lot of customization depending on your need. We can take a closer look at the second query, so that you can modify it.

(Select Max(CommandId) from tbl_Command

where Application not like 'Team Foundation JobAgent'

Group By IdentityName )

    • I am grouping by Identity name to select distinct entries of user ids
    • I have excluded 'Team Foundation JobAgent'- so that the TFSjobagent’s activities don’t get listed here in this list.
    • CommandId is the primary key for this table. So I use this to run the outer query.

 

Bonus!

This query will give you the complete list of a user activity in the collection in the past.

Select IdentityName AS ID,

StartTime AS Last_Access_Time,

Command as Reason,

IPAddress as IP,

ExecutionTime as Time

from tbl_Command where IdentityName like 'domain\user' order by Last_Access_Time desc

 

Things to note –

  1. These queries give info for a collection. You may have to run this against all collection databases for a complete server level report.
  2. All time is in UTC

Content created by Venkata Narasimhan

Content reviewed by Romit Gulati