In my last post, I wrote about the mechanism within Team Foundation that records web method activity. You can mine the database to and learn much about how Team Foundation is being used. Recall that the web method information is written to a database so the full power of SQL queries is available.

List all Team Foundation activity

Combine the command and parameter table (the left join is necessary since some web method rows in the command table may not record parameter information -- more on this later)

select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid

List all Team Foundation Version Control activity

Same as the previous query with the addition of filtering on the Application column (command table).

select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid
-- Just Version Control (other choices: Data Warehouse, Integration, Work Item Tracking, Proxy)
where c.Application = 'Version Control'

Show the web methods with the longest execution time

The ExecutionTime column contains the execution time, in microseconds, of the web method.

select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid
order by c.ExecutionTime desc

Show the web methods called the most

select command, count (command) as TimesCalled
from tbl_command c
group by command
order by TimesCalled desc

Show the web method calls made by userN

The IdentityName column contains the Windows account name of the caller.

select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid
-- IdentityName contains the account name of the user (domain\account)
where c.IdentityName like '%userN%'

Show the web method calls made from Visual Studio

The UserAgent column contains the name of the executable from which the web method call originates.

select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid
-- UserAgent contains the executable name
where c.Useragent like '%devenv.exe%'

Other tidbits

• Rows in the parameter table contain the commandId value of the corresponding row in the command table. Note that there may be multiple rows in the parameter table for a single command
• Parameters are recorded when (1) An error occurs during web method execution (indicated by the Status column of the command table set to -1); (2) The web method took longer than 30 seconds to execute (recall that the ExecutionTime values are in microseconds) or (3) the Web method logging level is set to All in the web.config file.