All.

I had made a post in the TFS Reporting forum, and was directed here for better feedback; http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=624073&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

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

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