Reporting against TfsVersionControl  
Author Message
Chuck Miller





PostPosted: Team Foundation Server - Version Control, Reporting against TfsVersionControl Top

All.

I had made a post in the TFS Reporting forum, and was directed here for better feedback; http://www.hide-link.com/ ;SiteID=1&mode=1

I am looking to create audit reports, and found none "out of the box". So, I'm looking for suggestions (any and all) on querying this database. Particularly, I am looking for two reports; 1) Who has what checked out, 2) Check out history of an item(s).

Thanks,

Chuck



Visual Studio Team System41  
 
 
Douglas R





PostPosted: Team Foundation Server - Version Control, Reporting against TfsVersionControl Top

I think the preferred method of doing this is with the object model. However, I pieced this query together that may be a decent starting point. I don't think this data is available in the Cube, so I wrote it against the relational tables.

This seems to work for 'who has what checked out"

select targetserveritem, i.displayname from tbl_pendingchange p

inner join tbl_workspace w

on p.workspaceid = w.workspaceid

inner join tbl_identity i

on w.ownerid = i.IdentityID

where w.[type]=0

The type=0 is there to filter out shelvesets (at least that is what I could tell the type was for based on the workspace names I saw).

Doug R


 
 
Chuck Miller





PostPosted: Team Foundation Server - Version Control, Reporting against TfsVersionControl Top

Doug,

Thanks for the reply. You attacked this query from a different perspective than did I, which is a good thing. I went looking for rows in the tbl_Version, as below (I had this in my original post). It gives a slightly different rowcount, so I need to verify just why. Thanks again for your input.

Chuck

SELECT MAX(V.VersionFrom) VersionFrom,V.ItemId,V.FullPath,I.DisplayName,L.LockStatus,L.LockType

FROM dbo.tbl_Version V (NOLOCK)

INNER JOIN dbo.tbl_Lock L (NOLOCK) ON V.FullPath = L.FullPath

INNER JOIN dbo.tbl_Workspace W (NOLOCK) ON L.WorkspaceId = W.WorkspaceId

INNER JOIN dbo.tbl_Identity I (NOLOCK) ON W.OwnerId = I.IdentityId

GROUP BY V.ItemId,V.FullPath,I.DisplayName,L.LockStatus,L.LockType


 
 
eugene.z





PostPosted: Team Foundation Server - Version Control, Reporting against TfsVersionControl Top

G'day,

I think the data that you need are available only through object model (have a look at our tool; it makes available "who has what checked out" and "check out history of an item" data).

As for data that are available through the cube, look up the following article in MSDN. While that does not give you current check out status, I believe the cube has sufficient data to build a history report (through file and changeset tables).

The temptation to get dirty with relational database is almost unbearable, but I personally resorted to make maximum use of object model and code churn cube. As of now, the data there were sufficient.

Yours truly,



 
 
Chuck Miller





PostPosted: Team Foundation Server - Version Control, Reporting against TfsVersionControl Top

Thanks for your reply. I will definitely look at TFS SideKick tools you mention. While I don't mind writing SQL and have been doing it for a while, I agree that coding directly against the physical schema is risky, at best. I will also take a closer look at the Code Churn cube.