--View SQL Jobs Last Status in System Center
Declare @SQLServer varchar(250) = '' Declare @SQLJobName varchar(250) = '' SELECT bme.Path, bme.DisplayName as 'Entity Display Name', mv.DisplayName AS 'Monitor Display Name', Case s.HealthState When 1 Then 'Success' When 2 Then 'Failed' Else '?' End As 'Health State', DateAdd(HH, -4, s.LastModified) AS 'State Last Modified', mv.AlertOnState, mv.AlertPriority, mv.AlertSeverity, mv.AlertAutoResolve, mv.Name AS 'Monitor Name', bme.FullName AS 'Target' FROM [OperationsManager].[dbo].[State] s JOIN BaseManagedEntity bme ON s.BaseManagedEntityId = bme.BaseManagedEntityId JOIN MonitorView mv ON mv.Id = s.MonitorId WHERE bme.[Path] Like '%MSSQLSERVER%' and mv.DisplayName = 'Last Run Status' and (@SQLServer = '' or [Path] Like '%' + @SQLServer + '%') and (@SQLJobName = '' or bme.DisplayName = @SQLJobName) Order By --bme.Path ASC, --bme.DisplayName ASC s.LastModified Desc
|
|
|
|
|