ODR.Report.Script.AlertsPerDayReportDataGet (DataWarehouseScript)

Element properties:

Source Code:

<DataWarehouseScript ID="ODR.Report.Script.AlertsPerDayReportDataGet" Accessibility="Public">

  <Install> 

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

  BEGIN

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

  END

GO



ALTER PROCEDURE dbo.[ODR_Report_Library_AlertsPerDayReportDataGet]

  @StartDate datetime,

  @EndDate datetime

AS

BEGIN

  SET NOCOUNT ON



  DECLARE @Error int

  DECLARE @ExecError int

  DECLARE @MinuteDifference int

  DECLARE @DayStartDate datetime



  SET @MinuteDifference = DATEDIFF(minute, getutcdate(), getdate()) -- get time zone difference in minutes

  SET @DayStartDate = DATEADD(minute, @MinuteDifference, @StartDate) -- convert utc to user time

  SET @DayStartDate = DATEDIFF(day, 0, @DayStartDate) -- convert to 0 hour of user time

  SET @DayStartDate = DATEADD(minute, -@MinuteDifference, @DayStartDate) -- convert user time to utc



  SET @Error = @@ERROR

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



  SET @Error = @@ERROR

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



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



  CREATE TABLE #TempAlert

  (

    AlertName nvarchar(256),

    RaisedDateTime datetime,

    ManagementPackSystemName nvarchar(256),

    WorkflowSystemName nvarchar(256)

  )



  SET @Error = @@ERROR

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



  INSERT INTO #TempAlert

  SELECT

    Alert.vAlert.AlertName AS AlertName,

    DATEDIFF(day, 0, DATEADD(minute, @MinuteDifference, Alert.vAlert.RaisedDateTime)) AS RaisedDateTime,

    vManagementPack.ManagementPackSystemName AS ManagementPackSystemName,

    ISNULL(vMonitor.MonitorSystemName, vRule.RuleSystemName) AS WorkflowSystemName

  FROM

    Alert.vAlert

  INNER JOIN

    vManagedEntity ON Alert.vAlert.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId

  INNER JOIN

    vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId

  INNER JOIN

    vManagementPack ON vManagementPack.ManagementPackRowId = vManagedEntityType.ManagementPackRowId

  LEFT OUTER JOIN

    vMonitor ON Alert.vAlert.WorkflowRowId = vMonitor.MonitorRowId

  LEFT OUTER JOIN

    vRule ON Alert.vAlert.WorkflowRowId = vRule.RuleRowId

  WHERE

    Alert.vAlert.RaisedDateTime &gt;= @DayStartDate AND Alert.vAlert.RaisedDateTime &lt; @EndDate



  SET @Error = @@ERROR

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



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



  SELECT TOP 1000

    #TempAlert.AlertName,

    COUNT(#TempAlert.WorkflowSystemName) AS AlertCount,

    #TempAlert.RaisedDateTime,

    #TempAlert.ManagementPackSystemName,

    #TempAlert.WorkflowSystemName

  FROM

    #TempAlert

  GROUP BY

    #TempAlert.WorkflowSystemName,

    #TempAlert.AlertName,

    #TempAlert.ManagementPackSystemName,

    #TempAlert.RaisedDateTime

  ORDER BY

    RaisedDateTime DESC,

    AlertCount DESC



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



  SET @Error = @@ERROR



QuitError:

  DROP TABLE #TempAlert

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[ODR_Report_Library_AlertsPerDayReportDataGet] TO OpsMgrReader

GO



                </Install>

  <Uninstall>

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

	BEGIN

        DROP PROCEDURE dbo.[ODR_Report_Library_AlertsPerDayReportDataGet]

	END

GO



                </Uninstall>

  <Upgrade> 

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

  BEGIN

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

  END

GO



ALTER PROCEDURE dbo.[ODR_Report_Library_AlertsPerDayReportDataGet]

  @StartDate datetime,

  @EndDate datetime

AS

BEGIN

  SET NOCOUNT ON



  DECLARE @Error int

  DECLARE @ExecError int

  DECLARE @MinuteDifference int

  DECLARE @DayStartDate datetime



  SET @MinuteDifference = DATEDIFF(minute, getutcdate(), getdate()) -- get time zone difference in minutes

  SET @DayStartDate = DATEADD(minute, @MinuteDifference, @StartDate) -- convert utc to user time

  SET @DayStartDate = DATEDIFF(day, 0, @DayStartDate) -- convert to 0 hour of user time

  SET @DayStartDate = DATEADD(minute, -@MinuteDifference, @DayStartDate) -- convert user time to utc



  SET @Error = @@ERROR

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



  SET @Error = @@ERROR

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



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



  CREATE TABLE #TempAlert

  (

    AlertName nvarchar(256),

    RaisedDateTime datetime,

    ManagementPackSystemName nvarchar(256),

    WorkflowSystemName nvarchar(256)

  )



  SET @Error = @@ERROR

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



  INSERT INTO #TempAlert

  SELECT

    Alert.vAlert.AlertName AS AlertName,

    DATEDIFF(day, 0, DATEADD(minute, @MinuteDifference, Alert.vAlert.RaisedDateTime)) AS RaisedDateTime,

    vManagementPack.ManagementPackSystemName AS ManagementPackSystemName,

    ISNULL(vMonitor.MonitorSystemName, vRule.RuleSystemName) AS WorkflowSystemName

  FROM

    Alert.vAlert

  INNER JOIN

    vManagedEntity ON Alert.vAlert.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId

  INNER JOIN

    vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId

  INNER JOIN

    vManagementPack ON vManagementPack.ManagementPackRowId = vManagedEntityType.ManagementPackRowId

  LEFT OUTER JOIN

    vMonitor ON Alert.vAlert.WorkflowRowId = vMonitor.MonitorRowId

  LEFT OUTER JOIN

    vRule ON Alert.vAlert.WorkflowRowId = vRule.RuleRowId

  WHERE

    Alert.vAlert.RaisedDateTime &gt;= @DayStartDate AND Alert.vAlert.RaisedDateTime &lt; @EndDate



  SET @Error = @@ERROR

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



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



  SELECT TOP 1000

    #TempAlert.AlertName,

    COUNT(#TempAlert.WorkflowSystemName) AS AlertCount,

    #TempAlert.RaisedDateTime,

    #TempAlert.ManagementPackSystemName,

    #TempAlert.WorkflowSystemName

  FROM

    #TempAlert

  GROUP BY

    #TempAlert.WorkflowSystemName,

    #TempAlert.AlertName,

    #TempAlert.ManagementPackSystemName,

    #TempAlert.RaisedDateTime

  ORDER BY

    RaisedDateTime DESC,

    AlertCount DESC



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



  SET @Error = @@ERROR



QuitError:

  DROP TABLE #TempAlert

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[ODR_Report_Library_AlertsPerDayReportDataGet] TO OpsMgrReader

GO



                </Upgrade>

</DataWarehouseScript>