Microsoft.SystemCenter.DataWarehouse.Report.Script.MostCommonEventsGraph (DataWarehouseScript)

Element properties:

Source Code:

<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



--===============================================================

-- CREATE TEMP TABLES

--===============================================================

CREATE TABLE #tmpObjectList (ManagedEntityRowId INT)

CREATE TABLE #tmpEventList ( EventOriginId UNIQUEIDENTIFIER )



--HANDLE ERRORS

SET @Error = @@ERROR

IF @Error &lt;&gt; 0 OR @ExecError &lt;&gt; 0 GOTO QuitError



--===============================================================

-- INSERT OBJECT ID's INTO TEMP TABLES

--===============================================================

INSERT INTO #tmpObjectList (ManagedEntityRowId)

EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

        @ObjectList = @ObjectList,

        @StartDate = @StartDate,

        @EndDate = @EndDate



--===============================================================

-- 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 &gt;= @StartDate) AND (vEvent.DateTime &lt; @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 &lt;&gt; 0 OR @ExecError &lt;&gt; 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) &lt;&gt;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 &lt;= @Threshold

ORDER BY CASE

            WHEN ISNULL(@TotalEvents,0) &lt;&gt;0 THEN (_vEvent.RepeatCount/@TotalEvents)

            ELSE 0

         END DESC



--HANDLE ERRORS

SET @Error = @@ERROR

IF @Error &lt;&gt; 0 OR @ExecError &lt;&gt; 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



--===============================================================

-- CREATE TEMP TABLES

--===============================================================

CREATE TABLE #tmpObjectList (ManagedEntityRowId INT)

CREATE TABLE #tmpEventList ( EventOriginId UNIQUEIDENTIFIER )



--HANDLE ERRORS

SET @Error = @@ERROR

IF @Error &lt;&gt; 0 OR @ExecError &lt;&gt; 0 GOTO QuitError



--===============================================================

-- INSERT OBJECT ID's INTO TEMP TABLES

--===============================================================

INSERT INTO #tmpObjectList (ManagedEntityRowId)

EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

        @ObjectList = @ObjectList,

        @StartDate = @StartDate,

        @EndDate = @EndDate



--===============================================================

-- 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 &gt;= @StartDate) AND (vEvent.DateTime &lt; @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 &lt;&gt; 0 OR @ExecError &lt;&gt; 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) &lt;&gt;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 &lt;= @Threshold

ORDER BY CASE

            WHEN ISNULL(@TotalEvents,0) &lt;&gt;0 THEN (_vEvent.RepeatCount/@TotalEvents)

            ELSE 0

         END DESC



--HANDLE ERRORS

SET @Error = @@ERROR

IF @Error &lt;&gt; 0 OR @ExecError &lt;&gt; 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>