Home
  •  

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

Element properties:

Install ScriptRes.Microsoft.SystemCenter.DataWarehouse.Report.Script.AlertDetail.Install
Uninstall ScriptRes.Microsoft.SystemCenter.DataWarehouse.Report.Script.AlertDetail.Uninstall
Upgrade ScriptRes.Microsoft.SystemCenter.DataWarehouse.Report.Script.AlertDetail.Upgrade
Upgrade UnsupportedFalse
AccessibilityPublic

Source Code:

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

  <Install> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertDetailReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

	@AlertSeverity xml,

	@AlertPriority xml,

	@SiteName nvarchar(256),

	@Owner 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



  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

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

  CREATE TABLE #TempAlert

	(

		AlertName nvarchar(256),

		AlertDescription nvarchar(max),

		AlertGuid uniqueidentifier,

		Severity tinyint,

		Priority tinyint,

		ManagedEntityRowId int,

		RaisedDateTime datetime,

		SiteName nvarchar(256), 

		AlertProblemGuid uniqueidentifier



	)	



  SET @Error = @@ERROR

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



  INSERT INTO #TempAlert

	SELECT 

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

		Alert.vAlert.RaisedDateTime,

		Alert.vAlert.SiteName, Alert.vAlert.AlertProblemGuid

	FROM 

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

	WHERE 

		 

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

		Alert.vAlert.RaisedDateTime &lt; @EndDate AND 

		Alert.vAlert.Severity IN 

		(

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

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

		) AND 

		Alert.vAlert.Priority IN 

		(

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

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

		) AND

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

	



  SET @Error = @@ERROR

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

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

	

SELECT 

	#TempAlert.AlertName, #TempAlert.Severity,

    #TempAlert.Priority,

    #TempAlert.ManagedEntityRowId,

	#TempAlert.RaisedDateTime,#TempAlert.AlertDescription,

	#TempAlert.SiteName, #TempAlert.AlertProblemGuid, #TempAlert.AlertGuid,  



	Alert.vAlertDetail.Owner,Alert.vAlertDetail.TicketId,

	Alert.vAlertDetail.CustomField1,Alert.vAlertDetail.CustomField2,

	Alert.vAlertDetail.CustomField3,Alert.vAlertDetail.CustomField4,

	Alert.vAlertDetail.CustomField5,Alert.vAlertDetail.CustomField6,

	Alert.vAlertDetail.CustomField7,Alert.vAlertDetail.CustomField8,

	Alert.vAlertDetail.CustomField9,Alert.vAlertDetail.CustomField10,

	Alert.vAlertDetail.TfsWorkItemId,Alert.vAlertDetail.TfsWorkItemOwner,



	vManagedEntity.ManagedEntityDefaultName,vManagedEntity.ManagedEntityGuid,

	vManagedEntity.Path,



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



	vManagementGroup.ManagementGroupDefaultName,vManagementGroup.ManagementGroupGuid,vManagementGroup.ManagementGroupRowId,



	vManagedEntityTypeImage.Image,



	temp_AlertDetailCustomFieldHeader.*

FROM

#TempAlert INNER JOIN 

Alert.vAlertDetail ON #TempAlert.AlertGuid=Alert.vAlertDetail.AlertGuid AND ( @Owner IS NULL OR @Owner = Alert.vAlertDetail.Owner) 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 INNER JOIN

(SELECT 

F1.ManagementGroupRowId AS ManagementGroupRowId,

F1.DisplayName AS CustomFieldLabel1,

F2.DisplayName AS CustomFieldLabel2,

F3.DisplayName AS CustomFieldLabel3,

F4.DisplayName AS CustomFieldLabel4,

F5.DisplayName AS CustomFieldLabel5,

F6.DisplayName AS CustomFieldLabel6,

F7.DisplayName AS CustomFieldLabel7,

F8.DisplayName AS CustomFieldLabel8,

F9.DisplayName AS CustomFieldLabel9,

F10.DisplayName AS CustomFieldLabel10

FROM 

vAlertCustomField as F1 INNER JOIN 

vAlertCustomField as F2 ON	F1.ManagementGroupRowId = F2.ManagementGroupRowId AND 

							F1.Ordinal = 1 AND

							F2.Ordinal = 2 INNER JOIN

vAlertCustomField as F3 ON  F3.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F3.Ordinal = 3 INNER JOIN 

vAlertCustomField as F4 ON  F4.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F4.Ordinal = 4 INNER JOIN 

vAlertCustomField as F5 ON  F5.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F5.Ordinal = 5 INNER JOIN 

vAlertCustomField as F6 ON  F6.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F6.Ordinal = 6 INNER JOIN 

vAlertCustomField as F7 ON  F7.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F7.Ordinal = 7 INNER JOIN 

vAlertCustomField as F8 ON  F8.ManagementGroupRowId = F1.ManagementGroupRowId   AND

							F8.Ordinal = 8 INNER JOIN 

vAlertCustomField as F9 ON  F9.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F9.Ordinal = 9 INNER JOIN 

vAlertCustomField as F10 ON F10.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F10.Ordinal = 10

) AS temp_AlertDetailCustomFieldHeader ON temp_AlertDetailCustomFieldHeader.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId



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



  SET @Error = @@ERROR



QuitError:

  DROP TABLE #ObjectList

  DROP TABLE #TempAlert

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertDetailReportDataGet] TO OpsMgrReader

GO



                </Install>

  <Uninstall>

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

	BEGIN

        DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertDetailReportDataGet]

	END

GO



                </Uninstall>

  <Upgrade> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertDetailReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

	@AlertSeverity xml,

	@AlertPriority xml,

	@SiteName nvarchar(256),

	@Owner 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



  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

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

  CREATE TABLE #TempAlert

	(

		AlertName nvarchar(256),

		AlertDescription nvarchar(max),

		AlertGuid uniqueidentifier,

		Severity tinyint,

		Priority tinyint,

		ManagedEntityRowId int,

		RaisedDateTime datetime,

		SiteName nvarchar(256), 

		AlertProblemGuid uniqueidentifier



	)	



  SET @Error = @@ERROR

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



  INSERT INTO #TempAlert

	SELECT 

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

		Alert.vAlert.RaisedDateTime,

		Alert.vAlert.SiteName, Alert.vAlert.AlertProblemGuid

	FROM 

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

	WHERE 

		 

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

		Alert.vAlert.RaisedDateTime &lt; @EndDate AND 

		Alert.vAlert.Severity IN 

		(

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

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

		) AND 

		Alert.vAlert.Priority IN 

		(

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

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

		) AND

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

	



  SET @Error = @@ERROR

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

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

	

SELECT 

	#TempAlert.AlertName, #TempAlert.Severity,

    #TempAlert.Priority,

    #TempAlert.ManagedEntityRowId,

	#TempAlert.RaisedDateTime,#TempAlert.AlertDescription,

	#TempAlert.SiteName, #TempAlert.AlertProblemGuid, #TempAlert.AlertGuid,  



	Alert.vAlertDetail.Owner,Alert.vAlertDetail.TicketId,

	Alert.vAlertDetail.CustomField1,Alert.vAlertDetail.CustomField2,

	Alert.vAlertDetail.CustomField3,Alert.vAlertDetail.CustomField4,

	Alert.vAlertDetail.CustomField5,Alert.vAlertDetail.CustomField6,

	Alert.vAlertDetail.CustomField7,Alert.vAlertDetail.CustomField8,

	Alert.vAlertDetail.CustomField9,Alert.vAlertDetail.CustomField10,

	Alert.vAlertDetail.TfsWorkItemId,Alert.vAlertDetail.TfsWorkItemOwner,



	vManagedEntity.ManagedEntityDefaultName,vManagedEntity.ManagedEntityGuid,

	vManagedEntity.Path,



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



	vManagementGroup.ManagementGroupDefaultName,vManagementGroup.ManagementGroupGuid,vManagementGroup.ManagementGroupRowId,



	vManagedEntityTypeImage.Image,



	temp_AlertDetailCustomFieldHeader.*

FROM

#TempAlert INNER JOIN 

Alert.vAlertDetail ON #TempAlert.AlertGuid=Alert.vAlertDetail.AlertGuid AND ( @Owner IS NULL OR @Owner = Alert.vAlertDetail.Owner) 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 INNER JOIN

(SELECT 

F1.ManagementGroupRowId AS ManagementGroupRowId,

F1.DisplayName AS CustomFieldLabel1,

F2.DisplayName AS CustomFieldLabel2,

F3.DisplayName AS CustomFieldLabel3,

F4.DisplayName AS CustomFieldLabel4,

F5.DisplayName AS CustomFieldLabel5,

F6.DisplayName AS CustomFieldLabel6,

F7.DisplayName AS CustomFieldLabel7,

F8.DisplayName AS CustomFieldLabel8,

F9.DisplayName AS CustomFieldLabel9,

F10.DisplayName AS CustomFieldLabel10

FROM 

vAlertCustomField as F1 INNER JOIN 

vAlertCustomField as F2 ON	F1.ManagementGroupRowId = F2.ManagementGroupRowId AND 

							F1.Ordinal = 1 AND

							F2.Ordinal = 2 INNER JOIN

vAlertCustomField as F3 ON  F3.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F3.Ordinal = 3 INNER JOIN 

vAlertCustomField as F4 ON  F4.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F4.Ordinal = 4 INNER JOIN 

vAlertCustomField as F5 ON  F5.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F5.Ordinal = 5 INNER JOIN 

vAlertCustomField as F6 ON  F6.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F6.Ordinal = 6 INNER JOIN 

vAlertCustomField as F7 ON  F7.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F7.Ordinal = 7 INNER JOIN 

vAlertCustomField as F8 ON  F8.ManagementGroupRowId = F1.ManagementGroupRowId   AND

							F8.Ordinal = 8 INNER JOIN 

vAlertCustomField as F9 ON  F9.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F9.Ordinal = 9 INNER JOIN 

vAlertCustomField as F10 ON F10.ManagementGroupRowId = F1.ManagementGroupRowId  AND

							F10.Ordinal = 10

) AS temp_AlertDetailCustomFieldHeader ON temp_AlertDetailCustomFieldHeader.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId



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



  SET @Error = @@ERROR



QuitError:

  DROP TABLE #ObjectList

  DROP TABLE #TempAlert

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertDetailReportDataGet] TO OpsMgrReader

GO



                </Upgrade>

</DataWarehouseScript>