--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
|
|
|
|
|
|