<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.MostCommonAlerts" 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_MostCommonAlertsReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportDataGet] AS RETURN 1')
END
GO
--===============================================================
-- ALTER PROCEDURE
--===============================================================
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportDataGet]
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@Threshold INT = 4,
@ObjectList XML = NULL,
@LanguageCode varchar(3)= 'ENU'
AS
BEGIN
SET NOCOUNT ON
--===============================================================
-- DECLARE LOCAL VARIABLES
--===============================================================
DECLARE @TotalAlerts DECIMAL
DECLARE @Error INT
--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) )
GROUP BY vAlert.AlertGuid
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--===============================================================
-- SET LOCAL VARIABLES
--===============================================================
SET @TotalAlerts = (SELECT COUNT(*) FROM #tmpAlertList)
--===============================================================
-- FINAL RESULT SET
--===============================================================
SELECT _vAlert.WorkflowRowId,
_vAlert.AlertName,
_vAlert.ManagementPackRowId,
ISNULL(vDisplayStringMP.Name,vMP.ManagementPackDefaultName) AS MPName,
vMP.ManagementPackDefaultDescription,
_vAlert.MonitorGuid,
ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) AS MonitorName,
_vAlert.RuleGuid,
ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleName,
cast(_vAlert.TotalTTR as bigint),
cast(_vAlert.AverageTTR as bigint),
_vAlert.RepeatCount,
@TotalAlerts AS TotalAlerts,
CASE
WHEN ISNULL(@TotalAlerts,0) <> 0 THEN (_vAlert.RepeatCount/@TotalAlerts)
ELSE 0
END AS ActivityPct
FROM
(
SELECT subAlerts.WorkflowRowId,
subAlerts.AlertName,
vMP.ManagementPackRowId,
vMonitor.MonitorGuid,
vRule.RuleGuid,
SUM(cast(vAlertRS.TimeFromRaisedSeconds as bigint)) / 60 AS TotalTTR,
AVG(cast(vAlertRS.TimeFromRaisedSeconds as bigint)) / 60 AS AverageTTR,
COUNT(*) AS RepeatCount,
CASE
WHEN @Threshold=0 THEN 0
ELSE DENSE_RANK() OVER(PARTITION BY 1 ORDER BY COUNT(*) DESC)
END 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.WorkflowRowId,
subAlerts.AlertName,
vMP.ManagementPackRowId,
vMonitor.MonitorGuid,
vRule.RuleGuid
) AS _vAlert
LEFT OUTER JOIN dbo.vMonitor vMonitor ON _vAlert.MonitorGuid = vMonitor.MonitorGuid
LEFT OUTER JOIN dbo.vRule vRule ON _vAlert.RuleGuid = vRule.RuleGuid
INNER JOIN dbo.vManagementPack vMP ON _vAlert.ManagementPackRowId = vMP.ManagementPackRowId
LEFT OUTER JOIN dbo.vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid AND vDisplayStringMonitor.LanguageCode = @LanguageCode
LEFT OUTER JOIN dbo.vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND vDisplayStringRule.LanguageCode = @LanguageCode
LEFT OUTER JOIN dbo.vDisplayString vDisplayStringMP ON vMP.ManagementPackVersionIndependentGuid = vDisplayStringMP.ElementGuid AND vDisplayStringMP.LanguageCode = @LanguageCode
WHERE _vAlert.RowNum <= @Threshold
ORDER BY vMP.ManagementPackDefaultName 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_MostCommonAlertsReportDataGet] 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_MostCommonAlertsReportDataGet]
END
GO
</Uninstall> <Upgrade>
--===============================================================
-- DROP EXSITING PROCEDURE
--===============================================================
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportDataGet] AS RETURN 1')
END
GO
--===============================================================
-- ALTER PROCEDURE
--===============================================================
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonAlertsReportDataGet]
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@Threshold INT = 4,
@ObjectList XML = NULL,
@LanguageCode varchar(3)= 'ENU'
AS
BEGIN
SET NOCOUNT ON
--===============================================================
-- DECLARE LOCAL VARIABLES
--===============================================================
DECLARE @TotalAlerts DECIMAL
DECLARE @Error INT
--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) )
GROUP BY vAlert.AlertGuid
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--===============================================================
-- SET LOCAL VARIABLES
--===============================================================
SET @TotalAlerts = (SELECT COUNT(*) FROM #tmpAlertList)
--===============================================================
-- FINAL RESULT SET
--===============================================================
SELECT _vAlert.WorkflowRowId,
_vAlert.AlertName,
_vAlert.ManagementPackRowId,
ISNULL(vDisplayStringMP.Name,vMP.ManagementPackDefaultName) AS MPName,
vMP.ManagementPackDefaultDescription,
_vAlert.MonitorGuid,
ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) AS MonitorName,
_vAlert.RuleGuid,
ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleName,
cast(_vAlert.TotalTTR as bigint),
cast(_vAlert.AverageTTR as bigint),
_vAlert.RepeatCount,
@TotalAlerts AS TotalAlerts,
CASE
WHEN ISNULL(@TotalAlerts,0) <> 0 THEN (_vAlert.RepeatCount/@TotalAlerts)
ELSE 0
END AS ActivityPct
FROM
(
SELECT subAlerts.WorkflowRowId,
subAlerts.AlertName,
vMP.ManagementPackRowId,
vMonitor.MonitorGuid,
vRule.RuleGuid,
SUM(cast(vAlertRS.TimeFromRaisedSeconds as bigint)) / 60 AS TotalTTR,
AVG(cast(vAlertRS.TimeFromRaisedSeconds as bigint)) / 60 AS AverageTTR,
COUNT(*) AS RepeatCount,
CASE
WHEN @Threshold=0 THEN 0
ELSE DENSE_RANK() OVER(PARTITION BY 1 ORDER BY COUNT(*) DESC)
END 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.WorkflowRowId,
subAlerts.AlertName,
vMP.ManagementPackRowId,
vMonitor.MonitorGuid,
vRule.RuleGuid
) AS _vAlert
LEFT OUTER JOIN dbo.vMonitor vMonitor ON _vAlert.MonitorGuid = vMonitor.MonitorGuid
LEFT OUTER JOIN dbo.vRule vRule ON _vAlert.RuleGuid = vRule.RuleGuid
INNER JOIN dbo.vManagementPack vMP ON _vAlert.ManagementPackRowId = vMP.ManagementPackRowId
LEFT OUTER JOIN dbo.vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid AND vDisplayStringMonitor.LanguageCode = @LanguageCode
LEFT OUTER JOIN dbo.vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND vDisplayStringRule.LanguageCode = @LanguageCode
LEFT OUTER JOIN dbo.vDisplayString vDisplayStringMP ON vMP.ManagementPackVersionIndependentGuid = vDisplayStringMP.ElementGuid AND vDisplayStringMP.LanguageCode = @LanguageCode
WHERE _vAlert.RowNum <= @Threshold
ORDER BY vMP.ManagementPackDefaultName 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_MostCommonAlertsReportDataGet] TO OpsMgrReader
GO </Upgrade>
</DataWarehouseScript>
Home
