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

Element properties:

Source Code:

<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.Alert" Accessibility="Public">

  <Install> 

                 IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet')

	BEGIN

		EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet] AS RETURN 1')

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

	@Severity xml,

	@Priority xml,

	@SiteName nvarchar(256),

    @LanguageCode varchar(3) = 'ENU'

AS

BEGIN 

  SET NOCOUNT ON



  DECLARE @Error int

  DECLARE @ExecError int



  CREATE TABLE #ObjectList (ManagedEntityRowId int)



  SET @Error = @@ERROR

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



  CREATE TABLE #SeverityList (Severity tinyint)



  SET @Error = @@ERROR

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



  CREATE TABLE #PriorityList (Priority tinyint)



  SET @Error = @@ERROR

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



  CREATE TABLE #TempAlert

  (

	AlertName nvarchar(256) COLLATE database_default,

	Severity tinyint,

	Priority tinyint,

	ManagedEntityRowId int,

	LastRaisedTime datetime,

	FirstRaisedTime datetime,

    AlertDescription nvarchar(max) COLLATE database_default,

	RepeatCount int,

	SiteName nvarchar(256) COLLATE database_default, 

	AlertProblemGuid uniqueidentifier

  )	



  SET @Error = @@ERROR

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



  INSERT INTO #ObjectList (ManagedEntityRowId)

  EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

    @ObjectList = @ObjectList,

    @StartDate = @StartDate,

    @EndDate = @EndDate



  SET @Error = @@ERROR

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



  INSERT INTO #SeverityList (Severity)

  SELECT SeverityList.Severity.value('.','tinyint')

  FROM @Severity.nodes('/Data/Value') AS SeverityList(Severity)



  SET @Error = @@ERROR

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



  INSERT INTO #PriorityList (Priority)

  SELECT PriorityList.Priority.value('.','tinyint') AS Priority

  FROM @Priority.nodes('/Data/Value') AS PriorityList(Priority)



  SET @Error = @@ERROR

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



  INSERT INTO #TempAlert

	SELECT 

		Alert.vAlert.AlertName,Alert.vAlert.Severity,Alert.vAlert.Priority,Alert.vAlert.ManagedEntityRowId,

		MAX(Alert.vAlert.RaisedDateTime) AS LastRaisedTime,

		MIN(Alert.vAlert.RaisedDateTime) AS FirstRaisedTime,

        MIN(Alert.vAlert.AlertDescription) AS AlertDescription,

		COUNT(*) AS RepeatCount,

		Alert.vAlert.SiteName, 

		Alert.vAlert.AlertProblemGuid

	FROM 

		Alert.vAlert

        INNER JOIN #ObjectList ON #ObjectList.ManagedEntityRowId = Alert.vAlert.ManagedEntityRowId

        INNER JOIN #SeverityList ON #SeverityList.Severity = Alert.vAlert.Severity

        INNER JOIN #PriorityList ON #PriorityList.Priority = Alert.vAlert.Priority

	WHERE 

		Alert.vAlert.RaisedDateTime &gt;= @StartDate AND

		Alert.vAlert.RaisedDateTime &lt; @EndDate AND 

		(@SiteName IS NULL OR Alert.vAlert.SiteName = @SiteName)

	GROUP BY

		Alert.vAlert.AlertName,Alert.vAlert.Severity,Alert.vAlert.Priority,Alert.vAlert.ManagedEntityRowId,

		Alert.vAlert.SiteName, Alert.vAlert.AlertProblemGuid



  SET @Error = @@ERROR

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



/* ------------------------------ */



SELECT 

	TempAlert.AlertName, TempAlert.Severity,TempAlert.Priority, TempAlert.ManagedEntityRowId,

	TempAlert.LastRaisedTime, TempAlert.FirstRaisedTime,

    TempAlert.AlertDescription,	TempAlert.RepeatCount,

	TempAlert.SiteName, TempAlert.AlertProblemGuid,



	vManagedEntity.ManagedEntityDefaultName,vManagedEntity.ManagedEntityGuid,

	vManagedEntity.Path, 

    ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)AS DisplayName,

	vManagementGroup.ManagementGroupDefaultName,vManagementGroup.ManagementGroupGuid,

	vManagedEntityTypeImage.Image

FROM

	#TempAlert AS TempAlert INNER JOIN

    vManagedEntity ON TempAlert.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN 

    vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN

    vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId LEFT OUTER JOIN

	vManagedEntityTypeImage ON vManagedEntityTypeImage.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId AND

    vManagedEntityTypeImage.ImageCategory ='u16x16Icon' LEFT OUTER JOIN 

    vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND 

	vDisplayString.LanguageCode = @LanguageCode



  SET @Error = @@ERROR



QuitError:

  DROP TABLE #ObjectList

  DROP TABLE #SeverityList

  DROP TABLE #PriorityList

  DROP TABLE #TempAlert

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet] TO OpsMgrReader

GO



                </Install>

  <Uninstall>

                 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet')

	BEGIN

        DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet]

	END

GO



                </Uninstall>

  <Upgrade> 

                 IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet')

	BEGIN

		EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet] AS RETURN 1')

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

	@Severity xml,

	@Priority xml,

	@SiteName nvarchar(256),

    @LanguageCode varchar(3) = 'ENU'

AS

BEGIN 

  SET NOCOUNT ON



  DECLARE @Error int

  DECLARE @ExecError int



  CREATE TABLE #ObjectList (ManagedEntityRowId int)



  SET @Error = @@ERROR

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



  CREATE TABLE #SeverityList (Severity tinyint)



  SET @Error = @@ERROR

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



  CREATE TABLE #PriorityList (Priority tinyint)



  SET @Error = @@ERROR

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



  CREATE TABLE #TempAlert

  (

	AlertName nvarchar(256) COLLATE database_default,

	Severity tinyint,

	Priority tinyint,

	ManagedEntityRowId int,

	LastRaisedTime datetime,

	FirstRaisedTime datetime,

    AlertDescription nvarchar(max) COLLATE database_default,

	RepeatCount int,

	SiteName nvarchar(256) COLLATE database_default, 

	AlertProblemGuid uniqueidentifier

  )	



  SET @Error = @@ERROR

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



  INSERT INTO #ObjectList (ManagedEntityRowId)

  EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

    @ObjectList = @ObjectList,

    @StartDate = @StartDate,

    @EndDate = @EndDate



  SET @Error = @@ERROR

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



  INSERT INTO #SeverityList (Severity)

  SELECT SeverityList.Severity.value('.','tinyint')

  FROM @Severity.nodes('/Data/Value') AS SeverityList(Severity)



  SET @Error = @@ERROR

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



  INSERT INTO #PriorityList (Priority)

  SELECT PriorityList.Priority.value('.','tinyint') AS Priority

  FROM @Priority.nodes('/Data/Value') AS PriorityList(Priority)



  SET @Error = @@ERROR

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



  INSERT INTO #TempAlert

	SELECT 

		Alert.vAlert.AlertName,Alert.vAlert.Severity,Alert.vAlert.Priority,Alert.vAlert.ManagedEntityRowId,

		MAX(Alert.vAlert.RaisedDateTime) AS LastRaisedTime,

		MIN(Alert.vAlert.RaisedDateTime) AS FirstRaisedTime,

        MIN(Alert.vAlert.AlertDescription) AS AlertDescription,

		COUNT(*) AS RepeatCount,

		Alert.vAlert.SiteName, 

		Alert.vAlert.AlertProblemGuid

	FROM 

		Alert.vAlert

        INNER JOIN #ObjectList ON #ObjectList.ManagedEntityRowId = Alert.vAlert.ManagedEntityRowId

        INNER JOIN #SeverityList ON #SeverityList.Severity = Alert.vAlert.Severity

        INNER JOIN #PriorityList ON #PriorityList.Priority = Alert.vAlert.Priority

	WHERE 

		Alert.vAlert.RaisedDateTime &gt;= @StartDate AND

		Alert.vAlert.RaisedDateTime &lt; @EndDate AND 

		(@SiteName IS NULL OR Alert.vAlert.SiteName = @SiteName)

	GROUP BY

		Alert.vAlert.AlertName,Alert.vAlert.Severity,Alert.vAlert.Priority,Alert.vAlert.ManagedEntityRowId,

		Alert.vAlert.SiteName, Alert.vAlert.AlertProblemGuid



  SET @Error = @@ERROR

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



/* ------------------------------ */



SELECT 

	TempAlert.AlertName, TempAlert.Severity,TempAlert.Priority, TempAlert.ManagedEntityRowId,

	TempAlert.LastRaisedTime, TempAlert.FirstRaisedTime,

    TempAlert.AlertDescription,	TempAlert.RepeatCount,

	TempAlert.SiteName, TempAlert.AlertProblemGuid,



	vManagedEntity.ManagedEntityDefaultName,vManagedEntity.ManagedEntityGuid,

	vManagedEntity.Path, 

    ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)AS DisplayName,

	vManagementGroup.ManagementGroupDefaultName,vManagementGroup.ManagementGroupGuid,

	vManagedEntityTypeImage.Image

FROM

	#TempAlert AS TempAlert INNER JOIN

    vManagedEntity ON TempAlert.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN 

    vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN

    vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId LEFT OUTER JOIN

	vManagedEntityTypeImage ON vManagedEntityTypeImage.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId AND

    vManagedEntityTypeImage.ImageCategory ='u16x16Icon' LEFT OUTER JOIN 

    vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND 

	vDisplayString.LanguageCode = @LanguageCode



  SET @Error = @@ERROR



QuitError:

  DROP TABLE #ObjectList

  DROP TABLE #SeverityList

  DROP TABLE #PriorityList

  DROP TABLE #TempAlert

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet] TO OpsMgrReader

GO



                </Upgrade>

</DataWarehouseScript>