<DataWarehouseScript ID="Microsoft.SystemCenter.Visualization.Network.DataProviders.Script.GetTopNodesByAlerts" Accessibility="Internal"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts]
@ManagementGroup uniqueidentifier,
@ClassXml nvarchar(max),
@NumNodes int
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
/* ------------------------------ */
BEGIN TRY
DECLARE @ExecError int
-- Create #ClassTable to contain the GUIDs for all of the class types that we care about
CREATE TABLE #ClassTable(
Id uniqueidentifier)
-- Parse the ClassXml to get the GUIDs into the #ClassTable
DECLARE @xmlhandle int
INSERT INTO #ManagedEntityTable
SELECT DISTINCT ME.ManagedEntityRowId FROM vManagedEntity AS ME
INNER JOIN vManagedEntityType AS MET ON MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId
INNER JOIN #ClassTable AS CT ON CT.Id = MET.ManagedEntityTypeGuid
INNER JOIN vManagementGroup AS MG ON MG.ManagementGroupRowId = ME.ManagementGroupRowId
INNER JOIN vManagedEntityManagementGroup AS MEMG ON MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
WHERE MG.ManagementGroupGuid = @ManagementGroup
AND MEMG.ToDateTime IS null
-- #ManagedEntityTable is now set up
DECLARE @HostingRelationshipTypeRowId int
DECLARE @ContainmentRelationshipTypeRowId int
SELECT @HostingRelationshipTypeRowId = RT.RelationshipTypeRowId FROM vRelationshipType as RT
WHERE RT.RelationshipTypeSystemName = 'System.Hosting'
SELECT @ContainmentRelationshipTypeRowId = RT.RelationshipTypeRowId FROM vRelationshipType as RT
WHERE RT.RelationshipTypeSystemName = 'System.Containment'
INSERT INTO #HostedEntityTable
SELECT DISTINCT ME.ManagedEntityRowId AS ManagedEntityRowId, MET.ManagedEntityRowId AS HostManagedEntityRowId FROM vManagedEntity AS ME
INNER JOIN vRelationship AS R ON R.TargetManagedEntityRowId = ME.ManagedEntityRowId
INNER JOIN vRelationshipTypeManagementPackVersion AS RTMP ON RTMP.RelationshipTypeRowId = R.RelationshipTypeRowId
INNER JOIN vRelationshipType AS RT ON RT.RelationshipTypeRowId = RTMP.RelationshipTypeRowId
INNER JOIN #ManagedEntityTable AS MET ON MET.ManagedEntityRowId = R.SourceManagedEntityRowId
INNER JOIN vManagementGroup AS MG ON MG.ManagementGroupRowId = ME.ManagementGroupRowId
INNER JOIN vManagedEntityManagementGroup AS MEMG ON MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
WHERE MG.ManagementGroupGuid = @ManagementGroup
AND MEMG.ToDateTime IS null
AND (RTMP.BaseRelationshipTypeRowId = @ContainmentRelationshipTypeRowId
OR RTMP.BaseRelationshipTypeRowId = @HostingRelationshipTypeRowId)
-- Need to insert the host as well for any directly targeted alerts
INSERT INTO #HostedEntityTable
SELECT MET.ManagedEntityRowId, MET.ManagedEntityRowId FROM #ManagedEntityTable as MET
INSERT INTO #TopNTable(EntityRowId, NumAlerts)
SELECT TOP (@NumNodes) t.HostManagedEntityRowId, COUNT(t.HostManagedEntityRowId) AS NumAlerts FROM
(SELECT HET.ManagedEntityRowId as RowId, HET.HostManagedEntityRowId as HostManagedEntityRowId FROM vManagedEntity AS ME
INNER JOIN #HostedEntityTable AS HET ON HET.ManagedEntityRowId = ME.ManagedEntityRowId
INNER JOIN Alert.vAlert AS A ON A.ManagedEntityRowId = HET.ManagedEntityRowId
WHERE NOT EXISTS
(SELECT * FROM Alert.vAlertResolutionState as ARS
WHERE ARS.AlertGuid = A.AlertGuid
AND ARS.ResolutionState = 255)) AS t
GROUP BY t.HostManagedEntityRowId
ORDER BY NumAlerts DESC
DECLARE
@HealthState int
,@RowId int
SET @RowId = 0
WHILE EXISTS(SELECT * FROM #TopNTable AS TNT WHERE TNT.EntityRowId > @RowId)
BEGIN
SELECT TOP 1 @RowId = TNT.EntityRowId
FROM #TopNTable AS TNT
WHERE TNT.EntityRowId > @RowId
ORDER BY TNT.EntityRowId ASC
UPDATE #TopNTable SET
DisplayName = ME.DisplayName
,EntityGuid = ME.ManagedEntityGuid
,EntityState = @HealthState
FROM vManagedEntity AS ME
INNER JOIN #TopNTable AS TNT ON TNT.EntityRowId = ME.ManagedEntityRowId
WHERE ME.ManagedEntityRowId = @RowId
END
SELECT EntityGuid AS Id, DisplayName, EntityState AS State,NumAlerts FROM #TopNTable ORDER BY NumAlerts DESC
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts' AND UID = SCHEMA_ID('SDK'))
BEGIN
DROP PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts]
@ManagementGroup uniqueidentifier,
@ClassXml nvarchar(max),
@NumNodes int
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
/* ------------------------------ */
BEGIN TRY
DECLARE @ExecError int
-- Create #ClassTable to contain the GUIDs for all of the class types that we care about
CREATE TABLE #ClassTable(
Id uniqueidentifier)
-- Parse the ClassXml to get the GUIDs into the #ClassTable
DECLARE @xmlhandle int
INSERT INTO #ManagedEntityTable
SELECT DISTINCT ME.ManagedEntityRowId FROM vManagedEntity AS ME
INNER JOIN vManagedEntityType AS MET ON MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId
INNER JOIN #ClassTable AS CT ON CT.Id = MET.ManagedEntityTypeGuid
INNER JOIN vManagementGroup AS MG ON MG.ManagementGroupRowId = ME.ManagementGroupRowId
INNER JOIN vManagedEntityManagementGroup AS MEMG ON MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
WHERE MG.ManagementGroupGuid = @ManagementGroup
AND MEMG.ToDateTime IS null
-- #ManagedEntityTable is now set up
DECLARE @HostingRelationshipTypeRowId int
DECLARE @ContainmentRelationshipTypeRowId int
SELECT @HostingRelationshipTypeRowId = RT.RelationshipTypeRowId FROM vRelationshipType as RT
WHERE RT.RelationshipTypeSystemName = 'System.Hosting'
SELECT @ContainmentRelationshipTypeRowId = RT.RelationshipTypeRowId FROM vRelationshipType as RT
WHERE RT.RelationshipTypeSystemName = 'System.Containment'
INSERT INTO #HostedEntityTable
SELECT DISTINCT ME.ManagedEntityRowId AS ManagedEntityRowId, MET.ManagedEntityRowId AS HostManagedEntityRowId FROM vManagedEntity AS ME
INNER JOIN vRelationship AS R ON R.TargetManagedEntityRowId = ME.ManagedEntityRowId
INNER JOIN vRelationshipTypeManagementPackVersion AS RTMP ON RTMP.RelationshipTypeRowId = R.RelationshipTypeRowId
INNER JOIN vRelationshipType AS RT ON RT.RelationshipTypeRowId = RTMP.RelationshipTypeRowId
INNER JOIN #ManagedEntityTable AS MET ON MET.ManagedEntityRowId = R.SourceManagedEntityRowId
INNER JOIN vManagementGroup AS MG ON MG.ManagementGroupRowId = ME.ManagementGroupRowId
INNER JOIN vManagedEntityManagementGroup AS MEMG ON MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
WHERE MG.ManagementGroupGuid = @ManagementGroup
AND MEMG.ToDateTime IS null
AND (RTMP.BaseRelationshipTypeRowId = @ContainmentRelationshipTypeRowId
OR RTMP.BaseRelationshipTypeRowId = @HostingRelationshipTypeRowId)
-- Need to insert the host as well for any directly targeted alerts
INSERT INTO #HostedEntityTable
SELECT MET.ManagedEntityRowId, MET.ManagedEntityRowId FROM #ManagedEntityTable as MET
INSERT INTO #TopNTable(EntityRowId, NumAlerts)
SELECT TOP (@NumNodes) t.HostManagedEntityRowId, COUNT(t.HostManagedEntityRowId) AS NumAlerts FROM
(SELECT HET.ManagedEntityRowId as RowId, HET.HostManagedEntityRowId as HostManagedEntityRowId FROM vManagedEntity AS ME
INNER JOIN #HostedEntityTable AS HET ON HET.ManagedEntityRowId = ME.ManagedEntityRowId
INNER JOIN Alert.vAlert AS A ON A.ManagedEntityRowId = HET.ManagedEntityRowId
WHERE NOT EXISTS
(SELECT * FROM Alert.vAlertResolutionState as ARS
WHERE ARS.AlertGuid = A.AlertGuid
AND ARS.ResolutionState = 255)) AS t
GROUP BY t.HostManagedEntityRowId
ORDER BY NumAlerts DESC
DECLARE
@HealthState int
,@RowId int
SET @RowId = 0
WHILE EXISTS(SELECT * FROM #TopNTable AS TNT WHERE TNT.EntityRowId > @RowId)
BEGIN
SELECT TOP 1 @RowId = TNT.EntityRowId
FROM #TopNTable AS TNT
WHERE TNT.EntityRowId > @RowId
ORDER BY TNT.EntityRowId ASC
UPDATE #TopNTable SET
DisplayName = ME.DisplayName
,EntityGuid = ME.ManagedEntityGuid
,EntityState = @HealthState
FROM vManagedEntity AS ME
INNER JOIN #TopNTable AS TNT ON TNT.EntityRowId = ME.ManagedEntityRowId
WHERE ME.ManagedEntityRowId = @RowId
END
SELECT EntityGuid AS Id, DisplayName, EntityState AS State,NumAlerts FROM #TopNTable ORDER BY NumAlerts DESC
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SystemCenter_Visualization_Library_GetTopNodesByAlerts] TO OpsMgrReader
GO
Home
