Friday, 19 December 2014

TFS Job History –View Success Jobs

TFS Job monitoring is possible using https://YourTFSServer/tfs/_oi . More information on this can be found on here.
I have had a look at this and found some indications of failures.
a1
When I have a look at Job history I see the failure jobs.
a2
Job history shown here says successful jobs are not shown. and there is no way to filter and see them in here.
“The grid below shows the job history results over the stated period of time (up to 500 entries). Below is the list of filters which may be limiting the data which is returned. By default, the successful jobs ARE NOT shows to minimize distractions when reviewing the results.”
I thought there should be a way to see them since it is coming from TFS Databases for sure. Ok what I am going to explain below is how to view success jobs as well, but this is accessing TFS DB, make sure not to change any data here. View information would not harm anything :)
First I needed to find the Job I am interested in “LabManager VMM Server Background Synchronization Job”. I went through the tables of TFS_Configuration database and found a “tbl_JobDefinition” table but the job was not there. Then I decided to explore each collection DB and there was another “tbl_JobDefinition”. I executed below query on that and found the Job Definition.
a3 
Query 1
-------------------------------------------------------------------------------------------------------
SELECT TOP 1000 [PartitionId]
      ,[JobId]
      ,[JobName]
      ,[ExtensionName]
      ,[Data]
      ,[EnabledState]
      ,[Flags]
      ,[LastExecutionTime]
      ,[PriorityClass]
  FROM  [dbo].[tbl_JobDefinition]
  WHERE [JobName] = 'LabManager VMM Server Background Synchronization Job'
-------------------------------------------------------------------------------------------------------
Executed above for each collection and found that the jobid has same GUID for all the collections.
There was no Job History table in collection DBs. But in TFS_Configuration there is one. When tried with the GUID for “LabManager VMM Server Background Synchronization Job”, found the job history there, all successful and unsuccessful for the last one month period.
a4
Query 2
-------------------------------------------------------------------------------------------------------
SELECT TOP 1000 [HistoryId]
      ,[JobSource]
      ,[JobId]
      ,[QueueTime]
      ,[StartTime]
      ,[EndTime]
      ,[AgentId]
      ,[Result]
      ,[ResultMessage]
      ,[QueuedReasons]
      ,[QueueFlags]
      ,[Priority]
  FROM [Tfs_Configuration].[dbo].[tbl_JobHistory]
  where [JobId] = '1039066B-8187-4831-BCB5-CFC981D98140'
  order by [StartTime] asc
-------------------------------------------------------------------------------------------------------
I thought the Job Source should be the collection, Modified the query to link with “tbl_CatalogServiceReference, ”“tbl_CatalogResource” and “tbl_CatalogResourceType” got it confirmed.
a5
Query 3
-------------------------------------------------------------------------------------------------------
SELECT        TOP (1000) H.HistoryId,
                H.StartTime,
                H.EndTime,
                H.Result,
                H.ResultMessage,
                CR.DisplayName AS Resource,
                CRT.DisplayName AS [Resource Type]
FROM            [Tfs_Configuration].dbo.tbl_JobHistory H
                    INNER JOIN
                         [Tfs_Configuration].dbo.tbl_CatalogServiceReference CSR
                         ON CSR.ServiceIdentifier = H.JobSource
                         INNER JOIN
                         [Tfs_Configuration].dbo.tbl_CatalogResource CR
                         ON CSR.ResourceIdentifier = CR.Identifier
                         INNER JOIN
                         [Tfs_Configuration].dbo.tbl_CatalogResourceType CRT
                         ON CRT.Identifier = CR.ResourceType
WHERE        (H.JobId = '1039066B-8187-4831-BCB5-CFC981D98140')
ORDER BY H.StartTime
-------------------------------------------------------------------------------------------------------
If you are trying this out REMEMBER you are accessing TFS configuration and collection DBs. So BE CAREFUL not to edit any data.
















No comments: