<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.MostCommonAlertsGraph" Accessibility="Public" DataWarehouseDataSet="DataWarehouse!Microsoft.SystemCenter.DataWarehouse.DataSet.Alert"> <Install>
--===============================================================
-- DROP EXSITING PROCEDURE
--===============================================================
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportGraphDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportGraphDataGet] AS RETURN 1')
END
GO
--===============================================================
-- ALTER PROCEDURE
--===============================================================
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportGraphDataGet]
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@Threshold INT = 4,
@ObjectList XML = NULL
AS
BEGIN
SET NOCOUNT ON
--===============================================================
-- DECLARE LOCAL VARIABLES
--===============================================================
DECLARE @TotalAlerts DECIMAL
DECLARE @Error INT
--===============================================================
-- RESET @Threshold
--===============================================================
SET @Threshold = CASE
WHEN @Threshold = 0 OR @Threshold > 25 THEN 25
ELSE @Threshold
END
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--===============================================================
-- INSERT OBJECT ID's INTO TEMP TABLES
--===============================================================
INSERT INTO #tmpObjectList (ManagementPackRowId)
SELECT ObjectList.ManagementPackRowId.value('.', 'int')
FROM @ObjectList.nodes('/Data/Value') AS ObjectList(ManagementPackRowId)
--===============================================================
-- INSERT ALERT ID's INTO TEMP TABLES
--===============================================================
INSERT INTO #tmpAlertList ( AlertGuid )
SELECT DISTINCT vAlert.AlertGuid
FROM Alert.vAlert vAlert
LEFT OUTER JOIN dbo.vMonitor vMonitor ON (vAlert.WorkflowRowId = vMonitor.MonitorRowId)
AND (vAlert.MonitorAlertInd = 1)
LEFT OUTER JOIN dbo.vRule vRule ON (vAlert.WorkflowRowId = vRule.RuleRowId)
AND (vAlert.MonitorAlertInd = 0)
INNER JOIN dbo.vManagementPack vMP ON (CASE
WHEN vAlert.MonitorAlertInd = 1 THEN vMonitor.ManagementPackRowId
ELSE vRule.ManagementPackRowId
END) = vMP.ManagementPackRowId
INNER JOIN #tmpObjectList ON vMP.ManagementPackRowId = #tmpObjectList.ManagementPackRowId
WHERE --Filter by datetime
( (vAlert.RaisedDateTime >= @StartDate) AND (vAlert.RaisedDateTime < @EndDate) )
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--===============================================================
-- SET LOCAL VARIABLES
--===============================================================
SET @TotalAlerts = (SELECT COUNT(*) FROM #tmpAlertList)
--===============================================================
-- FINAL RESULT SET
--===============================================================
SELECT _vAlert.RowNum,
_vAlert.AlertName,
_vAlert.RepeatCount,
_vAlert.TotalTTR,
_vAlert.AverageTTR,
@TotalAlerts AS TotalAlerts,
CASE
WHEN ISNULL(@TotalAlerts,0) <> 0 THEN (_vAlert.RepeatCount/@TotalAlerts)
ELSE 0
END AS ActivityPct
FROM
(
SELECT subAlerts.AlertName,
SUM(vAlertRS.TimeFromRaisedSeconds) / 60 AS TotalTTR,
AVG(vAlertRS.TimeFromRaisedSeconds) / 60 AS AverageTTR,
COUNT(*) AS RepeatCount,
RANK() OVER(PARTITION BY 1 ORDER BY COUNT(*) DESC) AS RowNum
FROM Alert.vAlert subAlerts
INNER JOIN #tmpAlertList ON subAlerts.AlertGuid = #tmpAlertList.AlertGuid
LEFT OUTER JOIN dbo.vMonitor vMonitor ON (subAlerts.WorkflowRowId = vMonitor.MonitorRowId)
AND (subAlerts.MonitorAlertInd = 1)
LEFT OUTER JOIN dbo.vRule vRule ON (subAlerts.WorkflowRowId = vRule.RuleRowId)
AND (subAlerts.MonitorAlertInd = 0)
LEFT OUTER JOIN Alert.vAlertResolutionState vAlertRS ON subAlerts.AlertGuid = vAlertRS.AlertGuid
AND vAlertRS.ResolutionState=255
INNER JOIN dbo.vManagementPack vMP ON (CASE
WHEN subAlerts.MonitorAlertInd = 1 THEN vMonitor.ManagementPackRowId
ELSE vRule.ManagementPackRowId
END) = vMP.ManagementPackRowId
GROUP BY subAlerts.AlertName
) AS _vAlert
WHERE _vAlert.RowNum <= @Threshold
ORDER BY CASE
WHEN ISNULL(@TotalAlerts,0) <> 0 THEN (_vAlert.RepeatCount/@TotalAlerts)
ELSE 0
END DESC
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--===============================================================
-- ERROR HANDELING AND TEMP CLEANUP
--===============================================================
QuitError:
DROP TABLE #tmpObjectList
DROP TABLE #tmpAlertList
RETURN @Error
END
GO
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportGraphDataGet] TO OpsMgrReader
GO </Install> <Uninstall>
EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportGraphDataGet]
END
GO
</Uninstall> <Upgrade>
--===============================================================
-- DROP EXSITING PROCEDURE
--===============================================================
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportGraphDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportGraphDataGet] AS RETURN 1')
END
GO
--===============================================================
-- ALTER PROCEDURE
--===============================================================
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportGraphDataGet]
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@Threshold INT = 4,
@ObjectList XML = NULL
AS
BEGIN
SET NOCOUNT ON
--===============================================================
-- DECLARE LOCAL VARIABLES
--===============================================================
DECLARE @TotalAlerts DECIMAL
DECLARE @Error INT
--===============================================================
-- RESET @Threshold
--===============================================================
SET @Threshold = CASE
WHEN @Threshold = 0 OR @Threshold > 25 THEN 25
ELSE @Threshold
END
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--===============================================================
-- INSERT OBJECT ID's INTO TEMP TABLES
--===============================================================
INSERT INTO #tmpObjectList (ManagementPackRowId)
SELECT ObjectList.ManagementPackRowId.value('.', 'int')
FROM @ObjectList.nodes('/Data/Value') AS ObjectList(ManagementPackRowId)
--===============================================================
-- INSERT ALERT ID's INTO TEMP TABLES
--===============================================================
INSERT INTO #tmpAlertList ( AlertGuid )
SELECT DISTINCT vAlert.AlertGuid
FROM Alert.vAlert vAlert
LEFT OUTER JOIN dbo.vMonitor vMonitor ON (vAlert.WorkflowRowId = vMonitor.MonitorRowId)
AND (vAlert.MonitorAlertInd = 1)
LEFT OUTER JOIN dbo.vRule vRule ON (vAlert.WorkflowRowId = vRule.RuleRowId)
AND (vAlert.MonitorAlertInd = 0)
INNER JOIN dbo.vManagementPack vMP ON (CASE
WHEN vAlert.MonitorAlertInd = 1 THEN vMonitor.ManagementPackRowId
ELSE vRule.ManagementPackRowId
END) = vMP.ManagementPackRowId
INNER JOIN #tmpObjectList ON vMP.ManagementPackRowId = #tmpObjectList.ManagementPackRowId
WHERE --Filter by datetime
( (vAlert.RaisedDateTime >= @StartDate) AND (vAlert.RaisedDateTime < @EndDate) )
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--===============================================================
-- SET LOCAL VARIABLES
--===============================================================
SET @TotalAlerts = (SELECT COUNT(*) FROM #tmpAlertList)
--===============================================================
-- FINAL RESULT SET
--===============================================================
SELECT _vAlert.RowNum,
_vAlert.AlertName,
_vAlert.RepeatCount,
_vAlert.TotalTTR,
_vAlert.AverageTTR,
@TotalAlerts AS TotalAlerts,
CASE
WHEN ISNULL(@TotalAlerts,0) <> 0 THEN (_vAlert.RepeatCount/@TotalAlerts)
ELSE 0
END AS ActivityPct
FROM
(
SELECT subAlerts.AlertName,
SUM(vAlertRS.TimeFromRaisedSeconds) / 60 AS TotalTTR,
AVG(vAlertRS.TimeFromRaisedSeconds) / 60 AS AverageTTR,
COUNT(*) AS RepeatCount,
RANK() OVER(PARTITION BY 1 ORDER BY COUNT(*) DESC) AS RowNum
FROM Alert.vAlert subAlerts
INNER JOIN #tmpAlertList ON subAlerts.AlertGuid = #tmpAlertList.AlertGuid
LEFT OUTER JOIN dbo.vMonitor vMonitor ON (subAlerts.WorkflowRowId = vMonitor.MonitorRowId)
AND (subAlerts.MonitorAlertInd = 1)
LEFT OUTER JOIN dbo.vRule vRule ON (subAlerts.WorkflowRowId = vRule.RuleRowId)
AND (subAlerts.MonitorAlertInd = 0)
LEFT OUTER JOIN Alert.vAlertResolutionState vAlertRS ON subAlerts.AlertGuid = vAlertRS.AlertGuid
AND vAlertRS.ResolutionState=255
INNER JOIN dbo.vManagementPack vMP ON (CASE
WHEN subAlerts.MonitorAlertInd = 1 THEN vMonitor.ManagementPackRowId
ELSE vRule.ManagementPackRowId
END) = vMP.ManagementPackRowId
GROUP BY subAlerts.AlertName
) AS _vAlert
WHERE _vAlert.RowNum <= @Threshold
ORDER BY CASE
WHEN ISNULL(@TotalAlerts,0) <> 0 THEN (_vAlert.RepeatCount/@TotalAlerts)
ELSE 0
END DESC
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--===============================================================
-- ERROR HANDELING AND TEMP CLEANUP
--===============================================================
QuitError:
DROP TABLE #tmpObjectList
DROP TABLE #tmpAlertList
RETURN @Error
END
GO
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportGraphDataGet] TO OpsMgrReader
GO </Upgrade>
</DataWarehouseScript>