<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.MostCommonEventsGraph" Accessibility="Public" DataWarehouseDataSet="DataWarehouse!Microsoft.SystemCenter.DataWarehouse.DataSet.Event"> <Install>
--===============================================================
-- DROP EXSITING PROCEDURE
--===============================================================
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportGraphDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportGraphDataGet] AS RETURN 1')
END
GO
--===============================================================
-- ALTER PROCEDURE
--===============================================================
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportGraphDataGet]
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@Threshold INT = 4,
@ObjectList XML = NULL,
@SelectedSource NVARCHAR(255) = '0',
@SelectedType NVARCHAR(255) = '',
@SelectedCategory NVARCHAR(255) = '-1'
AS
BEGIN
SET NOCOUNT ON
--===============================================================
-- DECLARE LOCAL VARIABLES
--===============================================================
DECLARE @TotalEvents DECIMAL
DECLARE @Error INT
DECLARE @ExecError INT
--===============================================================
-- RESET @Threshold
--===============================================================
SET @SelectedType = CASE
WHEN @SelectedType = -1 THEN ''
ELSE @SelectedType
END
--===============================================================
-- INSERT EVENT ID's INTO TEMP TABLES
--===============================================================
INSERT INTO #tmpEventList ( EventOriginId )
SELECT vEvent.EventOriginId
FROM Event.vEvent vEvent
INNER JOIN (SELECT subRule.EventOriginId,
subRule.ManagedEntityRowId,
MAX(subRule.RuleRowId) AS RuleRowId
FROM Event.vEventRule subRule
GROUP BY subRule.EventOriginId,
subRule.ManagedEntityRowId) vEventRule ON vEvent.EventOriginId = vEventRule.EventOriginId
INNER JOIN #tmpObjectList ON vEventRule.ManagedEntityRowId = #tmpObjectList.ManagedEntityRowId
INNER JOIN vEventPublisher ON vEventPublisher.EventPublisherRowId = vEvent.EventPublisherRowId
--filter by source
AND ( @SelectedSource = 0 OR vEventPublisher.EventPublisherRowId = @SelectedSource )
INNER JOIN vEventCategory ON vEventCategory.EventCategoryRowId = vEvent.EventCategoryRowId
--filter by category
AND ( @SelectedCategory=-1 OR @SelectedCategory = vEventCategory.EventCategoryId )
WHERE --Filter by datetime,eventID,eventType
( (vEvent.DateTime >= @StartDate) AND (vEvent.DateTime < @EndDate) ) AND
( @SelectedType='' OR @SelectedType=(CASE WHEN vEvent.EventLevelId=0 THEN 4 ELSE vEvent.EventLevelId END ) )
GROUP BY vEvent.EventOriginId
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
--===============================================================
-- SET LOCAL VARIABLES
--===============================================================
SET @TotalEvents = (SELECT COUNT(*) FROM #tmpEventList)
--===============================================================
-- FINAL RESULT SET
--===============================================================
SELECT _vEvent.RowNum,
_vEvent.EventDisplayNumber,
_vEvent.RepeatCount,
@TotalEvents AS TotalEvents,
CASE
WHEN ISNULL(@TotalEvents,0) <>0 THEN (_vEvent.RepeatCount/@TotalEvents)
ELSE 0
END AS ActivityPct,
vEventLevel.EventLevelTitle
FROM
(
SELECT CASE
WHEN ISNULL(subEvents.EventLevelId,0)=0 THEN 4 -- Reset Null and Zeros to be Information Events
ELSE subEvents.EventLevelId
END AS EventLevelId,
subEvents.EventDisplayNumber,
COUNT(*) AS RepeatCount,
CASE
WHEN @Threshold=0 THEN 0
ELSE RANK() OVER(PARTITION BY 1 ORDER BY COUNT(*) DESC)
END AS RowNum
FROM Event.vEvent subEvents
INNER JOIN #tmpEventList ON subEvents.EventOriginId = #tmpEventList.EventOriginId
INNER JOIN (SELECT subRule.EventOriginId,
subRule.ManagedEntityRowId,
MAX(subRule.RuleRowId) AS RuleRowId
FROM Event.vEventRule subRule
GROUP BY subRule.EventOriginId,
subRule.ManagedEntityRowId) vEventRule ON subEvents.EventOriginId = vEventRule.EventOriginId
GROUP BY subEvents.EventDisplayNumber,
subEvents.EventLevelId
)AS _vEvent
INNER JOIN vEventLevel ON vEventLevel.EventLevelId = _vEvent.EventLevelId
WHERE _vEvent.RowNum <= @Threshold
ORDER BY CASE
WHEN ISNULL(@TotalEvents,0) <>0 THEN (_vEvent.RepeatCount/@TotalEvents)
ELSE 0
END DESC
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
--===============================================================
-- ERROR HANDELING AND TEMP CLEANUP
--===============================================================
QuitError:
DROP TABLE #tmpObjectList
DROP TABLE #tmpEventList
RETURN @Error
END
GO
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportGraphDataGet] 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_MostCommonEventsReportGraphDataGet]
END
GO
</Uninstall> <Upgrade>
--===============================================================
-- DROP EXSITING PROCEDURE
--===============================================================
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportGraphDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportGraphDataGet] AS RETURN 1')
END
GO
--===============================================================
-- ALTER PROCEDURE
--===============================================================
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportGraphDataGet]
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@Threshold INT = 4,
@ObjectList XML = NULL,
@SelectedSource NVARCHAR(255) = '0',
@SelectedType NVARCHAR(255) = '',
@SelectedCategory NVARCHAR(255) = '-1'
AS
BEGIN
SET NOCOUNT ON
--===============================================================
-- DECLARE LOCAL VARIABLES
--===============================================================
DECLARE @TotalEvents DECIMAL
DECLARE @Error INT
DECLARE @ExecError INT
--===============================================================
-- RESET @Threshold
--===============================================================
SET @SelectedType = CASE
WHEN @SelectedType = -1 THEN ''
ELSE @SelectedType
END
--===============================================================
-- INSERT EVENT ID's INTO TEMP TABLES
--===============================================================
INSERT INTO #tmpEventList ( EventOriginId )
SELECT vEvent.EventOriginId
FROM Event.vEvent vEvent
INNER JOIN (SELECT subRule.EventOriginId,
subRule.ManagedEntityRowId,
MAX(subRule.RuleRowId) AS RuleRowId
FROM Event.vEventRule subRule
GROUP BY subRule.EventOriginId,
subRule.ManagedEntityRowId) vEventRule ON vEvent.EventOriginId = vEventRule.EventOriginId
INNER JOIN #tmpObjectList ON vEventRule.ManagedEntityRowId = #tmpObjectList.ManagedEntityRowId
INNER JOIN vEventPublisher ON vEventPublisher.EventPublisherRowId = vEvent.EventPublisherRowId
--filter by source
AND ( @SelectedSource = 0 OR vEventPublisher.EventPublisherRowId = @SelectedSource )
INNER JOIN vEventCategory ON vEventCategory.EventCategoryRowId = vEvent.EventCategoryRowId
--filter by category
AND ( @SelectedCategory=-1 OR @SelectedCategory = vEventCategory.EventCategoryId )
WHERE --Filter by datetime,eventID,eventType
( (vEvent.DateTime >= @StartDate) AND (vEvent.DateTime < @EndDate) ) AND
( @SelectedType='' OR @SelectedType=(CASE WHEN vEvent.EventLevelId=0 THEN 4 ELSE vEvent.EventLevelId END ) )
GROUP BY vEvent.EventOriginId
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
--===============================================================
-- SET LOCAL VARIABLES
--===============================================================
SET @TotalEvents = (SELECT COUNT(*) FROM #tmpEventList)
--===============================================================
-- FINAL RESULT SET
--===============================================================
SELECT _vEvent.RowNum,
_vEvent.EventDisplayNumber,
_vEvent.RepeatCount,
@TotalEvents AS TotalEvents,
CASE
WHEN ISNULL(@TotalEvents,0) <>0 THEN (_vEvent.RepeatCount/@TotalEvents)
ELSE 0
END AS ActivityPct,
vEventLevel.EventLevelTitle
FROM
(
SELECT CASE
WHEN ISNULL(subEvents.EventLevelId,0)=0 THEN 4 -- Reset Null and Zeros to be Information Events
ELSE subEvents.EventLevelId
END AS EventLevelId,
subEvents.EventDisplayNumber,
COUNT(*) AS RepeatCount,
CASE
WHEN @Threshold=0 THEN 0
ELSE RANK() OVER(PARTITION BY 1 ORDER BY COUNT(*) DESC)
END AS RowNum
FROM Event.vEvent subEvents
INNER JOIN #tmpEventList ON subEvents.EventOriginId = #tmpEventList.EventOriginId
INNER JOIN (SELECT subRule.EventOriginId,
subRule.ManagedEntityRowId,
MAX(subRule.RuleRowId) AS RuleRowId
FROM Event.vEventRule subRule
GROUP BY subRule.EventOriginId,
subRule.ManagedEntityRowId) vEventRule ON subEvents.EventOriginId = vEventRule.EventOriginId
GROUP BY subEvents.EventDisplayNumber,
subEvents.EventLevelId
)AS _vEvent
INNER JOIN vEventLevel ON vEventLevel.EventLevelId = _vEvent.EventLevelId
WHERE _vEvent.RowNum <= @Threshold
ORDER BY CASE
WHEN ISNULL(@TotalEvents,0) <>0 THEN (_vEvent.RepeatCount/@TotalEvents)
ELSE 0
END DESC
--HANDLE ERRORS
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
--===============================================================
-- ERROR HANDELING AND TEMP CLEANUP
--===============================================================
QuitError:
DROP TABLE #tmpObjectList
DROP TABLE #tmpEventList
RETURN @Error
END
GO
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportGraphDataGet] TO OpsMgrReader
GO </Upgrade>
</DataWarehouseScript>