IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet]
@MGPXML xml,
@StateXML xml,
@ObjectList xml,
@IncludeMaintanenceMode smallint
AS
BEGIN
SET NOCOUNT ON
Declare @Error int
Declare @CurrentTime datetime,
@CurrentDateTime datetime,
@BaseDateTime datetime
Set @BaseDateTime = getUTCdate()
Set @CurrentTime = DATEADD(hh,-1,@BaseDateTime)
Set @CurrentDateTime = CONVERT(VARCHAR(10) , @CurrentTime, 120) + ' ' + CONVERT(VARCHAR(2), DATEPART(hh,@CurrentTime)) + ':00:00.000'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get MANAGEMENT GROUPS To Filter
Insert Into #TmpMGPAgs
Select Mgp.ManagementGroupRowId,
Mgp.ManagementGroupGuid,
Mgp.ManagementGroupDefaultName
From @MGPXML.nodes('/*/Value') As MgpList(ManagementGroupGuid)
Inner Join vManagementGroup (NoLock) Mgp
On Mgp.ManagementGroupGuid = MgpList.ManagementGroupGuid.value('.','uniqueidentifier')
Order By ManagementGroupRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get States To Filter
Insert Into #TmpStateList
Select HltList.HealthState.value('.', 'int')
From @StateXML.nodes('/*/Value') As HltList(HealthState)
If @IncludeMaintanenceMode = 1
BEGIN
insert into #TmpStateList values (100)
insert into #TmpStateList values (101)
END
--Get Agent List
Insert Into #OnlyAgentList
Select Distinct Me.ManagedEntityDefaultName
From vManagedEntityPropertySet Mps
Inner Join vManagedEntityTypeProperty Ptp
On Mps.PropertyGuid = Ptp.PropertyGuid
And Ptp.PropertySystemName = 'IsAgent'
And Mps.PropertyValue = '1'
Inner Join vManagedEntity Me
On Me.ManagedEntityRowId = Mps.ManagedEntityRowId
Inner Join #TmpMGPAgs MGR
On MGR.ManagementGroupRowId = Me.ManagementGroupRowId
--Get UNIX Agent List
Insert Into #OnlyAgentList
Select Distinct Me.ManagedEntityDefaultName
From vManagedEntityPropertySet Mps
Inner Join vManagedEntityTypeProperty Ptp
On Mps.PropertyGuid = Ptp.PropertyGuid
And Ptp.PropertySystemName = 'AgentVersion'
And Mps.PropertyValue Is Not Null
Inner Join vManagedEntity Me
On Me.ManagedEntityRowId = Mps.ManagedEntityRowId
Inner Join #TmpMGPAgs MGR
On MGR.ManagementGroupRowId = Me.ManagementGroupRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Object list for the Selected Object Groups
Insert into #ObjectList
Select distinct
Rel.TargetManagedEntityRowId,
Men.ManagedEntityGuid,
Men.ManagementGroupRowId,
Men.ManagedEntityDefaultName
From Relationship Rel
Inner Join @ObjectList.nodes('/Data/Objects/Object') As ObjList(ManagedEntityRowId)
On Rel.SourceManagedEntityRowId = ObjList.ManagedEntityRowId.value('.','int')
And ObjList.ManagedEntityRowId.value('@Use','nvarchar(255)') = 'Containment'
Inner Join vManagedEntity Men
On Men.ManagedEntityRowId = Rel.TargetManagedEntityRowId
And Men.ManagementGroupRowId = Rel.ManagementGroupRowId
Inner Join vRelationshipType Rtp
On Rel.RelationshipTypeRowId = Rtp.RelationshipTypeRowId
And Rtp.RelationshipTypeSystemName in ('Microsoft.SystemCenter.InstanceGroupContainsEntities','Microsoft.SystemCenter.ComputerGroupContainsComputer')
--Get Object list for the Selected Objects
Insert into #ObjectList
Select distinct
Men.ManagedEntityRowId,
Men.ManagedEntityGuid,
Men.ManagementGroupRowId,
Men.ManagedEntityDefaultName
From vManagedEntityPropertySet (NoLock) Mps
Inner Join vManagedEntityTypeProperty (NoLock) Ptp
On Mps.PropertyGuid = Ptp.PropertyGuid
Inner Join vManagedEntity Men
On Men.ManagedEntityRowId = Mps.ManagedEntityRowId
Inner Join vManagedEntityType (NoLock) Mty
On Mty.ManagedentityTypeRowId = Men.ManagedentityTypeRowId
And Mty.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.HealthService'
Inner Join @ObjectList.nodes('/Data/Objects/Object') As ObjList(ManagedEntityRowId)
On Men.ManagedEntityRowId = ObjList.ManagedEntityRowId.value('.','int')
And ObjList.ManagedEntityRowId.value('@Use','nvarchar(255)') = 'Self'
insert into #TempAgentMonitor
Select distinct
Olt.ManagedEntityRowId,
Mtp.ManagedEntityTypeGuid,
Olt.ManagementGroupRowId,
MG.ManagementGroupGuid,
Mon.ManagedEntityMonitorRowId,
Olt.ManagedEntityDefaultName,
MG.ManagementGroupDefaultName,
DATEDIFF(HH, Mon.DWCreatedDateTime,@CurrentDateTime) As DurationSinceCreated
From #ObjectList Olt
Inner Join #TmpMGPAgs MG
On MG.ManagementGroupRowId = Olt.ManagementGroupRowId
Inner Join vManagedEntity Me
On Me.ManagedEntityRowId = Olt.ManagedEntityRowId
Inner Join vManagedEntitytype Mtp
On Mtp.ManagedEntityTypeRowId = Me.ManagedEntityTypeRowId
Inner Join vManagedEntityMonitor Mon
On Mon.ManagedEntityRowId = Olt.ManagedEntityRowId
Inner Join Monitor Mn
On Mn.MonitorRowId = Mon.MonitorRowId
And Mn.MonitorSystemName = 'System.Health.EntityState'
Insert into #TempMonitorStateRecord
select Mn.ManagedEntityMonitorRowId,
Case
When St.[DateTime] Is Null Then @CurrentDateTime
Else St.[DateTime]
End As [DateTime],
Case
When St.IntervalEndHealthState Is NULL Then 1
Else St.IntervalEndHealthState
End As IntervalEndHealthState,
Case
When St.[DateTime] Is Not Null Then '1'
Else '0'
End As RecordExists
From State.vStateHourly St
Right Outer Join #TempAgentMonitor Mn
On St.ManagedEntityMonitorRowId = Mn.ManagedEntityMonitorRowId
And datepart(year,St.[DateTime]) = datepart(year,@CurrentTime)
and datepart(month,St.[DateTime]) = datepart(month,@CurrentTime)
and datepart(day,St.[DateTime]) = datepart(day,@CurrentTime)
and datepart(hour,St.[DateTime]) = datepart(hour,@CurrentTime)
order by Mn.ManagedEntityMonitorRowId
Insert into #TempMonitorStateGreenRecord
select GS.ManagedEntityMonitorRowId,
Case
When GS.InGreenStateMilliseconds = 3600000 Then '1'
Else '0'
End GreenExists
from dbo.vStateHourlyFull GS
Right Outer Join #TempAgentMonitor Mn
On GS.ManagedEntityMonitorRowId = Mn.ManagedEntityMonitorRowId
And datepart(year,[DateTime]) = datepart(year,@CurrentTime)
and datepart(month,[DateTime]) = datepart(month,@CurrentTime)
and datepart(day,[DateTime]) = datepart(day,@CurrentTime)
and datepart(hour,[DateTime]) = datepart(hour,@CurrentTime)
order by Mn.ManagedEntityMonitorRowId
Insert into #TempMonitorStateGrayRecord
select GRY.ManagedEntityMonitorRowId,
Case
When GRY.HealthServiceUnavailableMilliseconds > 0 Then '1'
Else '0'
End GrayExists
from dbo.vStateHourlyFull GRY
Right Outer Join #TempAgentMonitor Mn
On GRY.ManagedEntityMonitorRowId = Mn.ManagedEntityMonitorRowId
And datepart(year,[DateTime]) = datepart(year,@CurrentTime)
and datepart(month,[DateTime]) = datepart(month,@CurrentTime)
and datepart(day,[DateTime]) = datepart(day,@CurrentTime)
and datepart(hour,[DateTime]) = datepart(hour,@CurrentTime)
order by Mn.ManagedEntityMonitorRowId
--Get Current Monitor States Info
Insert into #TempMGDates
select Mn.ManagedEntityMonitorRowId,
Case
When RE.RecordExists = '1' Then RE.[DateTime]
When RE.RecordExists = '0' Then @CurrentDateTime
End As [DateTime],
Case
When GY.GrayExists = '1' Then 50
When RE.RecordExists = '1' Then RE.IntervalEndHealthState
When GE.GreenExists = '0' And St.IntervalEndHealthState IS NOT NULL Then St.IntervalEndHealthState
When GE.GreenExists = '1' And St.IntervalEndHealthState IS NULL Then 1
When St.IntervalEndHealthState Is NULL Then 1
Else St.IntervalEndHealthState
End As IntervalEndHealthState,
Case
When St.InPlannedMaintenanceMilliseconds > 0 Or St.InUnplannedMaintenanceMilliseconds > 0 Then 1
Else 0
End As InMaintenanceMode
From State.vStateHourly St
Right Outer Join #TempAgentMonitor Mn
On St.ManagedEntityMonitorRowId = Mn.ManagedEntityMonitorRowId
And datepart(year,St.[DateTime]) = datepart(year,@CurrentTime)
and datepart(month,St.[DateTime]) = datepart(month,@CurrentTime)
and datepart(day,St.[DateTime]) = datepart(day,@CurrentTime)
and datepart(hour,St.[DateTime]) = datepart(hour,@CurrentTime)
Inner Join #TempMonitorStateRecord RE
On Mn.ManagedEntityMonitorRowId = RE.ManagedEntityMonitorRowId
Inner Join #TempMonitorStateGreenRecord GE
On Mn.ManagedEntityMonitorRowId = GE.ManagedEntityMonitorRowId
Inner Join #TempMonitorStateGrayRecord GY
On Mn.ManagedEntityMonitorRowId = GY.ManagedEntityMonitorRowId
order by St.[DateTime] desc
--Get Previous Monitor States Info
Insert into #TempMGDatesChanged
Select Lst.ManagedEntityMonitorRowId,
Lst.[DateTime],
Lst.IntervalEndHealthState
From
(
Select RANK() OVER(PARTITION BY St.ManagedEntityMonitorRowId, St.IntervalEndHealthState ORDER BY St.ManagedEntityMonitorRowId, St.[DateTime] Desc) AS 'RowNumber',
St.ManagedEntityMonitorRowId,
St.[DateTime],
St.IntervalEndHealthState
From State.vStateHourly St
Inner Join #TempMGDates Dt
On St.ManagedEntityMonitorRowId = Dt.ManagedEntityMonitorRowId
And St.IntervalEndHealthState <> Dt.IntervalEndHealthState
And (
( St.HealthServiceUnavailableMilliseconds > 0 And St.HealthServiceUnavailableMilliseconds < 3600000 ) Or
( St.HealthServiceUnavailableMilliseconds = 0 )
)
) as Lst
Where Lst.RowNumber = 1
--Get Unhealthy Health Services
Select Dt.IntervalEndHealthState As HealthState,
Dt.InMaintenanceMode As InMaintenanceMode,
Case
When Agl.ManagedEntityDefaultName IS NULL Then 0
Else 1
End As AgentFlag,
Mon.ManagedEntityDefaultName As AgentName,
Case
When Dc.ManagedEntityMonitorRowId IS NULL Then Convert(decimal(38,2), Mon.DurationSinceCreated )
Else Convert(decimal(38,2), DATEDIFF(hh,Dc.[DateTime] ,Dt.[DateTime]))
End As TimeInState,
Mon.ManagementGroupDefaultName As ManagementGroupDefaultName,
Mon.ManagedEntityTypeGuid As ManagedEntityTypeGuid,
Mon.ManagementGroupGuid As ManagementGroupGuid
From #TempAgentMonitor Mon
Inner Join #TempMGDates Dt
On Mon.ManagedEntityMonitorRowId = Dt.ManagedEntityMonitorRowId
Inner Join #TmpStateList Sl
ON Sl.HealthState = Dt.IntervalEndHealthState
Left Outer Join #TempMGDatesChanged Dc
On Dt.ManagedEntityMonitorRowId = Dc.ManagedEntityMonitorRowId
Left Outer Join #OnlyAgentList Agl
On Mon.ManagedEntityDefaultName = Agl.ManagedEntityDefaultName
Order By Sl.HealthState, Mon.ManagedEntityDefaultName
QuitError:
IF OBJECT_ID('tempdb..#TmpMGPAgs') IS NOT NULL
BEGIN
drop table #TmpMGPAgs
END
IF OBJECT_ID('tempdb..#TmpStateList') IS NOT NULL
BEGIN
drop table #TmpStateList
END
IF OBJECT_ID('tempdb..#OnlyAgentList') IS NOT NULL
BEGIN
drop table #OnlyAgentList
END
IF OBJECT_ID('tempdb..#ObjectList') IS NOT NULL
BEGIN
drop table #ObjectList
END
IF OBJECT_ID('tempdb..#TempAgentMonitor') IS NOT NULL
BEGIN
drop table #TempAgentMonitor
END
IF OBJECT_ID('tempdb..#TempMGDates') IS NOT NULL
BEGIN
drop table #TempMGDates
END
IF OBJECT_ID('tempdb..#TempMGDatesChanged') IS NOT NULL
BEGIN
drop table #TempMGDatesChanged
END
IF OBJECT_ID('tempdb..#TempMonitorStateRecord') IS NOT NULL
BEGIN
drop table #TempMonitorStateRecord
END
IF OBJECT_ID('tempdb..#TempMonitorStateGreenRecord') IS NOT NULL
BEGIN
drop table #TempMonitorStateGreenRecord
END
IF OBJECT_ID('tempdb..#TempMonitorStateGrayRecord') IS NOT NULL
BEGIN
drop table #TempMonitorStateGrayRecord
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet]
@MGPXML xml,
@StateXML xml,
@ObjectList xml,
@IncludeMaintanenceMode smallint
AS
BEGIN
SET NOCOUNT ON
Declare @Error int
Declare @CurrentTime datetime,
@CurrentDateTime datetime,
@BaseDateTime datetime
Set @BaseDateTime = getUTCdate()
Set @CurrentTime = DATEADD(hh,-1,@BaseDateTime)
Set @CurrentDateTime = CONVERT(VARCHAR(10) , @CurrentTime, 120) + ' ' + CONVERT(VARCHAR(2), DATEPART(hh,@CurrentTime)) + ':00:00.000'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get MANAGEMENT GROUPS To Filter
Insert Into #TmpMGPAgs
Select Mgp.ManagementGroupRowId,
Mgp.ManagementGroupGuid,
Mgp.ManagementGroupDefaultName
From @MGPXML.nodes('/*/Value') As MgpList(ManagementGroupGuid)
Inner Join vManagementGroup (NoLock) Mgp
On Mgp.ManagementGroupGuid = MgpList.ManagementGroupGuid.value('.','uniqueidentifier')
Order By ManagementGroupRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get States To Filter
Insert Into #TmpStateList
Select HltList.HealthState.value('.', 'int')
From @StateXML.nodes('/*/Value') As HltList(HealthState)
If @IncludeMaintanenceMode = 1
BEGIN
insert into #TmpStateList values (100)
insert into #TmpStateList values (101)
END
--Get Agent List
Insert Into #OnlyAgentList
Select Distinct Me.ManagedEntityDefaultName
From vManagedEntityPropertySet Mps
Inner Join vManagedEntityTypeProperty Ptp
On Mps.PropertyGuid = Ptp.PropertyGuid
And Ptp.PropertySystemName = 'IsAgent'
And Mps.PropertyValue = '1'
Inner Join vManagedEntity Me
On Me.ManagedEntityRowId = Mps.ManagedEntityRowId
Inner Join #TmpMGPAgs MGR
On MGR.ManagementGroupRowId = Me.ManagementGroupRowId
--Get UNIX Agent List
Insert Into #OnlyAgentList
Select Distinct Me.ManagedEntityDefaultName
From vManagedEntityPropertySet Mps
Inner Join vManagedEntityTypeProperty Ptp
On Mps.PropertyGuid = Ptp.PropertyGuid
And Ptp.PropertySystemName = 'AgentVersion'
And Mps.PropertyValue Is Not Null
Inner Join vManagedEntity Me
On Me.ManagedEntityRowId = Mps.ManagedEntityRowId
Inner Join #TmpMGPAgs MGR
On MGR.ManagementGroupRowId = Me.ManagementGroupRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Object list for the Selected Object Groups
Insert into #ObjectList
Select distinct
Rel.TargetManagedEntityRowId,
Men.ManagedEntityGuid,
Men.ManagementGroupRowId,
Men.ManagedEntityDefaultName
From Relationship Rel
Inner Join @ObjectList.nodes('/Data/Objects/Object') As ObjList(ManagedEntityRowId)
On Rel.SourceManagedEntityRowId = ObjList.ManagedEntityRowId.value('.','int')
And ObjList.ManagedEntityRowId.value('@Use','nvarchar(255)') = 'Containment'
Inner Join vManagedEntity Men
On Men.ManagedEntityRowId = Rel.TargetManagedEntityRowId
And Men.ManagementGroupRowId = Rel.ManagementGroupRowId
Inner Join vRelationshipType Rtp
On Rel.RelationshipTypeRowId = Rtp.RelationshipTypeRowId
And Rtp.RelationshipTypeSystemName in ('Microsoft.SystemCenter.InstanceGroupContainsEntities','Microsoft.SystemCenter.ComputerGroupContainsComputer')
--Get Object list for the Selected Objects
Insert into #ObjectList
Select distinct
Men.ManagedEntityRowId,
Men.ManagedEntityGuid,
Men.ManagementGroupRowId,
Men.ManagedEntityDefaultName
From vManagedEntityPropertySet (NoLock) Mps
Inner Join vManagedEntityTypeProperty (NoLock) Ptp
On Mps.PropertyGuid = Ptp.PropertyGuid
Inner Join vManagedEntity Men
On Men.ManagedEntityRowId = Mps.ManagedEntityRowId
Inner Join vManagedEntityType (NoLock) Mty
On Mty.ManagedentityTypeRowId = Men.ManagedentityTypeRowId
And Mty.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.HealthService'
Inner Join @ObjectList.nodes('/Data/Objects/Object') As ObjList(ManagedEntityRowId)
On Men.ManagedEntityRowId = ObjList.ManagedEntityRowId.value('.','int')
And ObjList.ManagedEntityRowId.value('@Use','nvarchar(255)') = 'Self'
insert into #TempAgentMonitor
Select distinct
Olt.ManagedEntityRowId,
Mtp.ManagedEntityTypeGuid,
Olt.ManagementGroupRowId,
MG.ManagementGroupGuid,
Mon.ManagedEntityMonitorRowId,
Olt.ManagedEntityDefaultName,
MG.ManagementGroupDefaultName,
DATEDIFF(HH, Mon.DWCreatedDateTime,@CurrentDateTime) As DurationSinceCreated
From #ObjectList Olt
Inner Join #TmpMGPAgs MG
On MG.ManagementGroupRowId = Olt.ManagementGroupRowId
Inner Join vManagedEntity Me
On Me.ManagedEntityRowId = Olt.ManagedEntityRowId
Inner Join vManagedEntitytype Mtp
On Mtp.ManagedEntityTypeRowId = Me.ManagedEntityTypeRowId
Inner Join vManagedEntityMonitor Mon
On Mon.ManagedEntityRowId = Olt.ManagedEntityRowId
Inner Join Monitor Mn
On Mn.MonitorRowId = Mon.MonitorRowId
And Mn.MonitorSystemName = 'System.Health.EntityState'
Insert into #TempMonitorStateRecord
select Mn.ManagedEntityMonitorRowId,
Case
When St.[DateTime] Is Null Then @CurrentDateTime
Else St.[DateTime]
End As [DateTime],
Case
When St.IntervalEndHealthState Is NULL Then 1
Else St.IntervalEndHealthState
End As IntervalEndHealthState,
Case
When St.[DateTime] Is Not Null Then '1'
Else '0'
End As RecordExists
From State.vStateHourly St
Right Outer Join #TempAgentMonitor Mn
On St.ManagedEntityMonitorRowId = Mn.ManagedEntityMonitorRowId
And datepart(year,St.[DateTime]) = datepart(year,@CurrentTime)
and datepart(month,St.[DateTime]) = datepart(month,@CurrentTime)
and datepart(day,St.[DateTime]) = datepart(day,@CurrentTime)
and datepart(hour,St.[DateTime]) = datepart(hour,@CurrentTime)
order by Mn.ManagedEntityMonitorRowId
Insert into #TempMonitorStateGreenRecord
select GS.ManagedEntityMonitorRowId,
Case
When GS.InGreenStateMilliseconds = 3600000 Then '1'
Else '0'
End GreenExists
from dbo.vStateHourlyFull GS
Right Outer Join #TempAgentMonitor Mn
On GS.ManagedEntityMonitorRowId = Mn.ManagedEntityMonitorRowId
And datepart(year,[DateTime]) = datepart(year,@CurrentTime)
and datepart(month,[DateTime]) = datepart(month,@CurrentTime)
and datepart(day,[DateTime]) = datepart(day,@CurrentTime)
and datepart(hour,[DateTime]) = datepart(hour,@CurrentTime)
order by Mn.ManagedEntityMonitorRowId
Insert into #TempMonitorStateGrayRecord
select GRY.ManagedEntityMonitorRowId,
Case
When GRY.HealthServiceUnavailableMilliseconds > 0 Then '1'
Else '0'
End GrayExists
from dbo.vStateHourlyFull GRY
Right Outer Join #TempAgentMonitor Mn
On GRY.ManagedEntityMonitorRowId = Mn.ManagedEntityMonitorRowId
And datepart(year,[DateTime]) = datepart(year,@CurrentTime)
and datepart(month,[DateTime]) = datepart(month,@CurrentTime)
and datepart(day,[DateTime]) = datepart(day,@CurrentTime)
and datepart(hour,[DateTime]) = datepart(hour,@CurrentTime)
order by Mn.ManagedEntityMonitorRowId
--Get Current Monitor States Info
Insert into #TempMGDates
select Mn.ManagedEntityMonitorRowId,
Case
When RE.RecordExists = '1' Then RE.[DateTime]
When RE.RecordExists = '0' Then @CurrentDateTime
End As [DateTime],
Case
When GY.GrayExists = '1' Then 50
When RE.RecordExists = '1' Then RE.IntervalEndHealthState
When GE.GreenExists = '0' And St.IntervalEndHealthState IS NOT NULL Then St.IntervalEndHealthState
When GE.GreenExists = '1' And St.IntervalEndHealthState IS NULL Then 1
When St.IntervalEndHealthState Is NULL Then 1
Else St.IntervalEndHealthState
End As IntervalEndHealthState,
Case
When St.InPlannedMaintenanceMilliseconds > 0 Or St.InUnplannedMaintenanceMilliseconds > 0 Then 1
Else 0
End As InMaintenanceMode
From State.vStateHourly St
Right Outer Join #TempAgentMonitor Mn
On St.ManagedEntityMonitorRowId = Mn.ManagedEntityMonitorRowId
And datepart(year,St.[DateTime]) = datepart(year,@CurrentTime)
and datepart(month,St.[DateTime]) = datepart(month,@CurrentTime)
and datepart(day,St.[DateTime]) = datepart(day,@CurrentTime)
and datepart(hour,St.[DateTime]) = datepart(hour,@CurrentTime)
Inner Join #TempMonitorStateRecord RE
On Mn.ManagedEntityMonitorRowId = RE.ManagedEntityMonitorRowId
Inner Join #TempMonitorStateGreenRecord GE
On Mn.ManagedEntityMonitorRowId = GE.ManagedEntityMonitorRowId
Inner Join #TempMonitorStateGrayRecord GY
On Mn.ManagedEntityMonitorRowId = GY.ManagedEntityMonitorRowId
order by St.[DateTime] desc
--Get Previous Monitor States Info
Insert into #TempMGDatesChanged
Select Lst.ManagedEntityMonitorRowId,
Lst.[DateTime],
Lst.IntervalEndHealthState
From
(
Select RANK() OVER(PARTITION BY St.ManagedEntityMonitorRowId, St.IntervalEndHealthState ORDER BY St.ManagedEntityMonitorRowId, St.[DateTime] Desc) AS 'RowNumber',
St.ManagedEntityMonitorRowId,
St.[DateTime],
St.IntervalEndHealthState
From State.vStateHourly St
Inner Join #TempMGDates Dt
On St.ManagedEntityMonitorRowId = Dt.ManagedEntityMonitorRowId
And St.IntervalEndHealthState <> Dt.IntervalEndHealthState
And (
( St.HealthServiceUnavailableMilliseconds > 0 And St.HealthServiceUnavailableMilliseconds < 3600000 ) Or
( St.HealthServiceUnavailableMilliseconds = 0 )
)
) as Lst
Where Lst.RowNumber = 1
--Get Unhealthy Health Services
Select Dt.IntervalEndHealthState As HealthState,
Dt.InMaintenanceMode As InMaintenanceMode,
Case
When Agl.ManagedEntityDefaultName IS NULL Then 0
Else 1
End As AgentFlag,
Mon.ManagedEntityDefaultName As AgentName,
Case
When Dc.ManagedEntityMonitorRowId IS NULL Then Convert(decimal(38,2), Mon.DurationSinceCreated )
Else Convert(decimal(38,2), DATEDIFF(hh,Dc.[DateTime] ,Dt.[DateTime]))
End As TimeInState,
Mon.ManagementGroupDefaultName As ManagementGroupDefaultName,
Mon.ManagedEntityTypeGuid As ManagedEntityTypeGuid,
Mon.ManagementGroupGuid As ManagementGroupGuid
From #TempAgentMonitor Mon
Inner Join #TempMGDates Dt
On Mon.ManagedEntityMonitorRowId = Dt.ManagedEntityMonitorRowId
Inner Join #TmpStateList Sl
ON Sl.HealthState = Dt.IntervalEndHealthState
Left Outer Join #TempMGDatesChanged Dc
On Dt.ManagedEntityMonitorRowId = Dc.ManagedEntityMonitorRowId
Left Outer Join #OnlyAgentList Agl
On Mon.ManagedEntityDefaultName = Agl.ManagedEntityDefaultName
Order By Sl.HealthState, Mon.ManagedEntityDefaultName
QuitError:
IF OBJECT_ID('tempdb..#TmpMGPAgs') IS NOT NULL
BEGIN
drop table #TmpMGPAgs
END
IF OBJECT_ID('tempdb..#TmpStateList') IS NOT NULL
BEGIN
drop table #TmpStateList
END
IF OBJECT_ID('tempdb..#OnlyAgentList') IS NOT NULL
BEGIN
drop table #OnlyAgentList
END
IF OBJECT_ID('tempdb..#ObjectList') IS NOT NULL
BEGIN
drop table #ObjectList
END
IF OBJECT_ID('tempdb..#TempAgentMonitor') IS NOT NULL
BEGIN
drop table #TempAgentMonitor
END
IF OBJECT_ID('tempdb..#TempMGDates') IS NOT NULL
BEGIN
drop table #TempMGDates
END
IF OBJECT_ID('tempdb..#TempMGDatesChanged') IS NOT NULL
BEGIN
drop table #TempMGDatesChanged
END
IF OBJECT_ID('tempdb..#TempMonitorStateRecord') IS NOT NULL
BEGIN
drop table #TempMonitorStateRecord
END
IF OBJECT_ID('tempdb..#TempMonitorStateGreenRecord') IS NOT NULL
BEGIN
drop table #TempMonitorStateGreenRecord
END
IF OBJECT_ID('tempdb..#TempMonitorStateGrayRecord') IS NOT NULL
BEGIN
drop table #TempMonitorStateGrayRecord
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_UnHealthyHealthServiceDataGet] TO OpsMgrReader
GO
Home
