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

Element properties:

Source Code:

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

  <Install> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventAnalysisReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

	@EventID int,

	@SelectedType nvarchar(255),

	@SelectedCategory nvarchar(255),

	@SelectedSource nvarchar(255),

    @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

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



DECLARE @SelectedTypeId int

SELECT @SelectedTypeId = EventLevelId from vEventLevel WHERE EventLevelTitle = @SelectedType



DECLARE @SelectedCategoryId int

SELECT @SelectedCategoryId = EventCategoryRowId from vEventCategory WHERE EventCategoryTitle = @SelectedCategory



DECLARE @SelectedSourceId int

SELECT @SelectedSourceId = EventPublisherRowId from vEventPublisher WHERE EventPublisherName =  @SelectedSource



SELECT 

_vEvent.EventDisplayNumber,_vEvent.FirstOccuranceTime,

_vEvent.LastOccuranceTime,_vEvent.RepeatCount,



ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName,



vEventLevel.EventLevelTitle,



vEventCategory.EventCategoryTitle,



vEventPublisher.EventPublisherName,



Event.vEventDetail.RenderedDescription,



vManagedEntity.ManagedEntityDefaultName,

vManagedEntity.ManagedEntityGuid,

vManagedEntity.Path,



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



vManagementGroup.ManagementGroupDefaultName,vManagementGroup.ManagementGroupGuid,



vManagedEntityTypeImage.Image

FROM 

(

	SELECT 

		Event.vEventRule.RuleRowId,

		Event.vEventRule.ManagedEntityRowId,

		EventPublisherRowId,

		EventCategoryRowId,EventLevelId,EventDisplayNumber,

		MIN(DateTime) AS FirstOccuranceTime,

		MAX(DateTime) AS LastOccuranceTime,	

		MIN(CONVERT(NVARCHAR(50),Event.vEvent.EventOriginId)) AS SampleEventOriginId,

		COUNT(*) AS RepeatCount

	FROM 

	Event.vEvent INNER JOIN

	Event.vEventRule ON Event.vEvent.EventOriginId = Event.vEventRule.EventOriginId INNER JOIN 

	#ObjectList ON Event.vEventRule.ManagedEntityRowId = #ObjectList.ManagedEntityRowId 

	WHERE 

		( Event.vEvent.DateTime &gt;= @StartDate) AND (Event.vEvent.DateTime &lt; @EndDate) AND 

		( @EventID IS NULL OR Event.vEvent.EventDisplayNumber= @EventID)AND

	    ( @SelectedType = '' OR   @SelectedTypeId =Event.vEvent.EventLevelId)

	GROUP BY 

		RuleRowId,Event.vEventRule.ManagedEntityRowId,EventPublisherRowId,

		EventCategoryRowId,EventLevelId,EventDisplayNumber

)AS _vEvent INNER JOIN 

vRule ON vRule.RuleRowId = _vEvent.RuleRowId INNER JOIN

vEventPublisher ON vEventPublisher.EventPublisherRowId = _vEvent.EventPublisherRowId 

		AND (@SelectedSource ='' OR vEventPublisher.EventPublisherRowId = @SelectedSourceId)INNER JOIN

vEventCategory ON vEventCategory.EventCategoryRowId = _vEvent.EventCategoryRowId 

		AND (  @SelectedCategory ='' OR @SelectedCategoryId = vEventCategory.EventCategoryRowId ) INNER JOIN

vEventLevel ON vEventLevel.EventLevelId = _vEvent.EventLevelId INNER JOIN

Event.vEventDetail ON Event.vEventDetail.EventOriginId = _vEvent.SampleEventOriginId INNER JOIN

vManagedEntity ON _vEvent.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 LEFT OUTER JOIN

vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND

vDisplayStringRule.LanguageCode = @LanguageCode 





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



  SET @Error = @@ERROR



QuitError:

  DROP TABLE #ObjectList

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventAnalysisReportDataGet] TO OpsMgrReader

GO



                </Install>

  <Uninstall>

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

	BEGIN

        DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventAnalysisReportDataGet]

	END

GO



                </Uninstall>

  <Upgrade> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventAnalysisReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

	@EventID int,

	@SelectedType nvarchar(255),

	@SelectedCategory nvarchar(255),

	@SelectedSource nvarchar(255),

    @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

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



DECLARE @SelectedTypeId int

SELECT @SelectedTypeId = EventLevelId from vEventLevel WHERE EventLevelTitle = @SelectedType



DECLARE @SelectedCategoryId int

SELECT @SelectedCategoryId = EventCategoryRowId from vEventCategory WHERE EventCategoryTitle = @SelectedCategory



DECLARE @SelectedSourceId int

SELECT @SelectedSourceId = EventPublisherRowId from vEventPublisher WHERE EventPublisherName =  @SelectedSource



SELECT 

_vEvent.EventDisplayNumber,_vEvent.FirstOccuranceTime,

_vEvent.LastOccuranceTime,_vEvent.RepeatCount,



ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName,



vEventLevel.EventLevelTitle,



vEventCategory.EventCategoryTitle,



vEventPublisher.EventPublisherName,



Event.vEventDetail.RenderedDescription,



vManagedEntity.ManagedEntityDefaultName,

vManagedEntity.ManagedEntityGuid,

vManagedEntity.Path,



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



vManagementGroup.ManagementGroupDefaultName,vManagementGroup.ManagementGroupGuid,



vManagedEntityTypeImage.Image

FROM 

(

	SELECT 

		Event.vEventRule.RuleRowId,

		Event.vEventRule.ManagedEntityRowId,

		EventPublisherRowId,

		EventCategoryRowId,EventLevelId,EventDisplayNumber,

		MIN(DateTime) AS FirstOccuranceTime,

		MAX(DateTime) AS LastOccuranceTime,	

		MIN(CONVERT(NVARCHAR(50),Event.vEvent.EventOriginId)) AS SampleEventOriginId,

		COUNT(*) AS RepeatCount

	FROM 

	Event.vEvent INNER JOIN

	Event.vEventRule ON Event.vEvent.EventOriginId = Event.vEventRule.EventOriginId INNER JOIN 

	#ObjectList ON Event.vEventRule.ManagedEntityRowId = #ObjectList.ManagedEntityRowId 

	WHERE 

		( Event.vEvent.DateTime &gt;= @StartDate) AND (Event.vEvent.DateTime &lt; @EndDate) AND 

		( @EventID IS NULL OR Event.vEvent.EventDisplayNumber= @EventID)AND

	    ( @SelectedType = '' OR   @SelectedTypeId =Event.vEvent.EventLevelId)

	GROUP BY 

		RuleRowId,Event.vEventRule.ManagedEntityRowId,EventPublisherRowId,

		EventCategoryRowId,EventLevelId,EventDisplayNumber

)AS _vEvent INNER JOIN 

vRule ON vRule.RuleRowId = _vEvent.RuleRowId INNER JOIN

vEventPublisher ON vEventPublisher.EventPublisherRowId = _vEvent.EventPublisherRowId 

		AND (@SelectedSource ='' OR vEventPublisher.EventPublisherRowId = @SelectedSourceId)INNER JOIN

vEventCategory ON vEventCategory.EventCategoryRowId = _vEvent.EventCategoryRowId 

		AND (  @SelectedCategory ='' OR @SelectedCategoryId = vEventCategory.EventCategoryRowId ) INNER JOIN

vEventLevel ON vEventLevel.EventLevelId = _vEvent.EventLevelId INNER JOIN

Event.vEventDetail ON Event.vEventDetail.EventOriginId = _vEvent.SampleEventOriginId INNER JOIN

vManagedEntity ON _vEvent.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 LEFT OUTER JOIN

vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND

vDisplayStringRule.LanguageCode = @LanguageCode 





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



  SET @Error = @@ERROR



QuitError:

  DROP TABLE #ObjectList

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventAnalysisReportDataGet] TO OpsMgrReader

GO



                </Upgrade>

</DataWarehouseScript>