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

Element properties:

Source Code:

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

  <Install> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventTemplateReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

	@InputXML xml,

	@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 #ColumnPicker 

  (

	PropertyID	nvarchar(100)	COLLATE database_default

	,Visible	nvarchar(16)	COLLATE database_default

	,[Position] int				

	,FilterText nvarchar(256)	COLLATE database_default

	,FilterValue nvarchar(255)	COLLATE database_default		

	,FilterType	nvarchar(255)	COLLATE database_default



  )

  DECLARE @xmldoc int

  EXEC @ExecError = sp_xml_preparedocument @xmldoc OUTPUT, @InputXML



  SET @Error = @@ERROR

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



  INSERT INTO #ColumnPicker (PropertyID, Visible, [Position], FilterText, FilterValue, FilterType)

	SELECT PropertyID, Visible, [Position],

 	       FilterText = PropertyID +

        CASE (FilterType)

		  WHEN('Equals') THEN  ('=''' + REPLACE(FilterValue,'''','''''') + '''')

		  WHEN('Contains')THEN (' LIKE ''%' + REPLACE(FilterValue,'''','''''') + '%''')

		  ELSE NULL

		END

	,FilterValue

	,FilterType

	  FROM OPENXML(@xmldoc, '/Data/Columns/Column', 2) WITH

			   (PropertyID		nvarchar(255)	'ID'			

				,Visible		nvarchar(255)	'@Visible'		

				,[Position]		int				'@mp:id'

				,FilterValue	nvarchar(255)	'Filter'		

				,FilterType	nvarchar(255) './Filter/@Type'	)

  WHERE PropertyID LIKE 'Parameter%' OR PropertyID IN (

'EventDisplayNumber',

'DateTime',

'RuleDefaultName',

'EventLevelTitle' ,

'EventChannelTitle' ,

'EventCategoryTitle',

'EventPublisherName' ,

'ManagedEntityDefaultName',

'ManagedEntityTypeDefaultName' ,

'ManagementGroupDefaultName' ,

'ComputerName',

'UserName')



  SET @Error = @@ERROR

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



--------------------------------------------------------------------------



  CREATE TABLE #ParameterFilter

  (

	 ParameterIndex	int 

	,ParameterValue nvarchar(255) 	COLLATE database_default		

  )



  INSERT INTO #ParameterFilter (ParameterIndex, ParameterValue)

	SELECT SUBSTRING(PropertyID, LEN('Parameter') + 1, LEN(PropertyID) - LEN('Parameter')),

 	ParameterValue = 

        CASE (FilterType)

		  WHEN('Equals') THEN  ('' + REPLACE(FilterValue,'''','''''') + '')

		  WHEN('Contains')THEN ('%' + REPLACE(FilterValue,'''','''''') + '%')

		  ELSE ''

		END

	FROM #ColumnPicker

	WHERE PropertyID LIKE 'Parameter%' AND FilterText IS NOT NULL



  SET @Error = @@ERROR

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



---------------------------------------------------------------------



--Build Column CSV List

DECLARE @ColumnList nvarchar(max)

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') 

+  CAST(PropertyID AS nvarchar(100))

FROM #ColumnPicker

WHERE  PropertyID NOT LIKE 'Parameter%'



--Get Parameter Column Count

DECLARE @ParameterColumnCount int

SELECT @ParameterColumnCount = Count(*) FROM #ColumnPicker WHERE Visible = 'true' AND PropertyID LIKE 'Parameter%'



-- Get Filter Count

DECLARE @FilterCount int

SELECT @FilterCount = Count(*) FROM #ColumnPicker 

WHERE FilterText IS NOT NULL AND PropertyID NOT LIKE 'Parameter%'



-- Build Filter CSV List

DECLARE @FilterList nvarchar(max)

SELECT @FilterList = COALESCE(@FilterList + ' AND ', '')

+  CAST(FilterText AS nvarchar(100)) 

FROM #ColumnPicker

WHERE FilterText IS NOT NULL AND PropertyID NOT LIKE 'Parameter%'



-- Build Parameter Filter Join

DECLARE @ParameterFilterJoin nvarchar(max)

SELECT @ParameterFilterJoin =

       ISNULL(@ParameterFilterJoin, '') + ' INNER JOIN

				 Event.vEventParameter AS EventParameter' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + ' ON EventParameter' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + '.EventOriginId = Event.vEvent.EventOriginId 

                 AND EventParameter' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + '.ParameterIndex = ' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + '

				 AND EventParameter' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + '.ParameterValue Like ''' + #ParameterFilter.ParameterValue + '''

                 '

FROM #ParameterFilter



---------------------------------------------------------------------

/* --- Insert into Temp Events Table --- */

CREATE TABLE #Events 

(

		EventOriginId				uniqueidentifier not null	

		,ManagedEntityRowId			int not null

		,EventDisplayNumber			nvarchar(255) 	COLLATE database_default 

		,DateTime					nvarchar(255)	COLLATE database_default

		,RuleDefaultName			nvarchar(255) 	COLLATE database_default

		,EventLevelTitle			nvarchar(255) 	COLLATE database_default

		,EventChannelTitle			nvarchar(255) 	COLLATE database_default

		,EventCategoryTitle			nvarchar(255) 	COLLATE database_default

		,EventPublisherName			nvarchar(255) 	COLLATE database_default

		,ManagedEntityDefaultName	nvarchar(255) 	COLLATE database_default

		,ManagedEntityTypeDefaultName	nvarchar(255) COLLATE database_default

		,ManagementGroupDefaultName	nvarchar(255) 	COLLATE database_default

		,ComputerName				nvarchar(255) 	COLLATE database_default

		,UserName					nvarchar(255) 	COLLATE database_default

)



DECLARE @Query nvarchar(max)

SELECT @Query= 

	'SELECT 

	Event.vEvent.EventOriginId,

	vManagedEntity.ManagedEntityRowId,

	Event.vEvent.EventDisplayNumber,

	Event.vEvent.DateTime,

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

	vEventLevel.EventLevelTitle ,

	vEventChannel.EventChannelTitle ,

	vEventCategory.EventCategoryTitle,

	vEventPublisher.EventPublisherName ,

	vManagedEntity.ManagedEntityDefaultName,

	vManagedEntityType.ManagedEntityTypeDefaultName ,

	vManagementGroup.ManagementGroupDefaultName ,

	vEventLoggingComputer.ComputerName,

	vEventUserName.UserName 

	FROM 

	Event.vEvent 

	INNER JOIN

	Event.vEventRule ON Event.vEvent.EventOriginId = Event.vEventRule.EventOriginId

	INNER JOIN

	vRule ON vRule.RuleRowId = Event.vEventRule.RuleRowId 

	INNER JOIN

	vEventPublisher ON vEventPublisher.EventPublisherRowId = Event.vEvent.EventPublisherRowId 

	INNER JOIN

	vEventCategory ON vEventCategory.EventCategoryRowId = Event.vEvent.EventCategoryRowId 

	INNER JOIN

	vEventLevel ON vEventLevel.EventLevelId = Event.vEvent.EventLevelId 

	INNER JOIN

	vManagedEntity ON Event.vEventRule.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId  

	INNER JOIN 

	vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId 

	INNER JOIN

	vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId 

	INNER JOIN 

	vEventLoggingComputer ON vEventLoggingComputer.EventLoggingComputerRowId = Event.vEvent.LoggingComputerRowId

	INNER JOIN

	vEventUserName ON vEventUserName.EventUserNameRowId = Event.vEvent.UserNameRowId

	INNER JOIN 

	vEventChannel ON vEventChannel.EventChannelRowId = Event.vEvent.EventChannelRowId

	INNER JOIN

    #ObjectList ON vManagedEntity.ManagedEntityRowId = #ObjectList.ManagedEntityRowId  

    LEFT OUTER JOIN

    vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid 

    AND 

    vDisplayString.LanguageCode ='''+ @LanguageCode +''''+ 



    ISNULL(@ParameterFilterJoin, '')



	+	'WHERE

						(Event.vEvent.DateTime &gt;='''+ CONVERT(nvarchar(100), @StartDate, 126) +''') AND 

						(Event.vEvent.DateTime &lt; '''+ CONVERT(nvarchar(100), @EndDate, 126) +''' )'



	+	CASE 

			WHEN (NOT @FilterCount = 0)

			THEN ' AND ' + @FilterList

			ELSE ''

		END



  INSERT INTO #Events

  EXECUTE(@Query)



  SET @Error = @@ERROR

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



---------------------------------------------------------------------



  CREATE TABLE #SelectedSet 

  (

		EventOriginId uniqueidentifier not null	

		,PropertyID	nvarchar(100) COLLATE database_default

		,PropertyValue nvarchar(256) COLLATE database_default

		,[Position] int

  )



  DECLARE @EventID uniqueidentifier

  SELECT TOP(1) @EventID= #Events.EventOriginId FROM #Events



  SET @Error = @@ERROR

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



  INSERT #SelectedSet		

  EXECUTE('SELECT 

			 unpvt.EventOriginId

			,#ColumnPicker.PropertyID

			,PropValue

			,#ColumnPicker.Position

		FROM 

			#Events

		UNPIVOT

	    (

		 PropValue FOR PropertyID IN ('+ @ColumnList +')

		) AS unpvt 

			INNER JOIN 

			#ColumnPicker ON unpvt.PropertyID = #ColumnPicker.PropertyID

			WHERE #ColumnPicker.Visible = ''true''')



  SET @Error = @@ERROR

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



  SELECT #Events.EventOriginId

		,#Events.ManagedEntityRowId

		,#Events.EventDisplayNumber

		,#Events.EventPublisherName

		,#SelectedSet.PropertyID

		,#SelectedSet.PropertyValue

		,#SelectedSet.Position

  FROM	#Events INNER JOIN 

		#SelectedSet ON #SelectedSet.EventOriginId = #Events.EventOriginId



  UNION



  SELECT #Events.EventOriginId

		,#Events.ManagedEntityRowId

		,#Events.EventDisplayNumber

		,#Events.EventPublisherName

		,#ColumnPicker.PropertyID

		,Event.vEventParameter.ParameterValue

		,#ColumnPicker.Position

  FROM 

		Event.vEventParameter INNER JOIN 

		#Events ON Event.vEventParameter.EventOriginId = #Events.EventOriginId INNER JOIN 

		#ColumnPicker ON Event.vEventParameter.ParameterIndex = CAST(SUBSTRING(#ColumnPicker.PropertyID, LEN('Parameter') + 1, LEN(#ColumnPicker.PropertyID) - LEN('Parameter')) AS int)

  WHERE 

		#ColumnPicker.PropertyID like 'Parameter%' and #ColumnPicker.Visible = 'true'



  UNION

	

  SELECT #Events.EventOriginId

		,#Events.ManagedEntityRowId

		,#Events.EventDisplayNumber

		,#Events.EventPublisherName

		,Properties.PropertyID

		,NULL

		,Properties.Position

  FROM

		#Events  

		CROSS JOIN

			( SELECT PropertyID, #ColumnPicker.Position

			  FROM  #ColumnPicker

			  WHERE #ColumnPicker.PropertyID LIKE 'Parameter%' AND #ColumnPicker.PropertyID  NOT IN

												(

													SELECT 'Parameter' + CONVERT(nvarchar(100),Event.vEventParameter.ParameterIndex)

													FROM Event.vEventParameter 

													INNER JOIN #Events ON Event.vEventParameter.EventOriginId = #Events.EventOriginId

													WHERE #Events.EventOriginId = @EventID

												)

			 ) AS Properties 

  WHERE #Events.EventOriginId = @EventID



  SET @Error = @@ERROR

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



------------------------------------------------------------------------------------------------



  SET @Error = @@ERROR

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

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



QuitError:

  IF ((@Error = 0) AND (@ExecError &lt;&gt; 0)) SET @Error = @ExecError



/* Clean Up */

  -- remove xml document if opened

IF @xmldoc IS NOT NULL EXEC sp_xml_removedocument @xmldoc



DROP TABLE #SelectedSet

DROP TABLE #Events

DROP TABLE #ParameterFilter

DROP TABLE #ColumnPicker

DROP TABLE #ObjectList



RETURN @Error



END

GO

 

GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventTemplateReportDataGet] TO OpsMgrReader

GO













                </Install>

  <Uninstall>

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

	BEGIN

        DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventTemplateReportDataGet]

	END

GO



                </Uninstall>

  <Upgrade> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventTemplateReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

	@InputXML xml,

	@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 #ColumnPicker 

  (

	PropertyID	nvarchar(100)	COLLATE database_default

	,Visible	nvarchar(16)	COLLATE database_default

	,[Position] int				

	,FilterText nvarchar(256)	COLLATE database_default

	,FilterValue nvarchar(255)	COLLATE database_default		

	,FilterType	nvarchar(255)	COLLATE database_default



  )

  DECLARE @xmldoc int

  EXEC @ExecError = sp_xml_preparedocument @xmldoc OUTPUT, @InputXML



  SET @Error = @@ERROR

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



  INSERT INTO #ColumnPicker (PropertyID, Visible, [Position], FilterText, FilterValue, FilterType)

	SELECT PropertyID, Visible, [Position],

 	       FilterText = PropertyID +

        CASE (FilterType)

		  WHEN('Equals') THEN  ('=''' + REPLACE(FilterValue,'''','''''') + '''')

		  WHEN('Contains')THEN (' LIKE ''%' + REPLACE(FilterValue,'''','''''') + '%''')

		  ELSE NULL

		END

	,FilterValue

	,FilterType

	  FROM OPENXML(@xmldoc, '/Data/Columns/Column', 2) WITH

			   (PropertyID		nvarchar(255)	'ID'			

				,Visible		nvarchar(255)	'@Visible'		

				,[Position]		int				'@mp:id'

				,FilterValue	nvarchar(255)	'Filter'		

				,FilterType	nvarchar(255) './Filter/@Type'	)

  WHERE PropertyID LIKE 'Parameter%' OR PropertyID IN (

'EventDisplayNumber',

'DateTime',

'RuleDefaultName',

'EventLevelTitle' ,

'EventChannelTitle' ,

'EventCategoryTitle',

'EventPublisherName' ,

'ManagedEntityDefaultName',

'ManagedEntityTypeDefaultName' ,

'ManagementGroupDefaultName' ,

'ComputerName',

'UserName')



  SET @Error = @@ERROR

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



--------------------------------------------------------------------------



  CREATE TABLE #ParameterFilter

  (

	 ParameterIndex	int 

	,ParameterValue nvarchar(255) 	COLLATE database_default		

  )



  INSERT INTO #ParameterFilter (ParameterIndex, ParameterValue)

	SELECT SUBSTRING(PropertyID, LEN('Parameter') + 1, LEN(PropertyID) - LEN('Parameter')),

 	ParameterValue = 

        CASE (FilterType)

		  WHEN('Equals') THEN  ('' + REPLACE(FilterValue,'''','''''') + '')

		  WHEN('Contains')THEN ('%' + REPLACE(FilterValue,'''','''''') + '%')

		  ELSE ''

		END

	FROM #ColumnPicker

	WHERE PropertyID LIKE 'Parameter%' AND FilterText IS NOT NULL



  SET @Error = @@ERROR

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



---------------------------------------------------------------------



--Build Column CSV List

DECLARE @ColumnList nvarchar(max)

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') 

+  CAST(PropertyID AS nvarchar(100))

FROM #ColumnPicker

WHERE  PropertyID NOT LIKE 'Parameter%'



--Get Parameter Column Count

DECLARE @ParameterColumnCount int

SELECT @ParameterColumnCount = Count(*) FROM #ColumnPicker WHERE Visible = 'true' AND PropertyID LIKE 'Parameter%'



-- Get Filter Count

DECLARE @FilterCount int

SELECT @FilterCount = Count(*) FROM #ColumnPicker 

WHERE FilterText IS NOT NULL AND PropertyID NOT LIKE 'Parameter%'



-- Build Filter CSV List

DECLARE @FilterList nvarchar(max)

SELECT @FilterList = COALESCE(@FilterList + ' AND ', '')

+  CAST(FilterText AS nvarchar(100)) 

FROM #ColumnPicker

WHERE FilterText IS NOT NULL AND PropertyID NOT LIKE 'Parameter%'



-- Build Parameter Filter Join

DECLARE @ParameterFilterJoin nvarchar(max)

SELECT @ParameterFilterJoin =

       ISNULL(@ParameterFilterJoin, '') + ' INNER JOIN

				 Event.vEventParameter AS EventParameter' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + ' ON EventParameter' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + '.EventOriginId = Event.vEvent.EventOriginId 

                 AND EventParameter' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + '.ParameterIndex = ' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + '

				 AND EventParameter' + CAST(#ParameterFilter.ParameterIndex AS nvarchar(100)) + '.ParameterValue Like ''' + #ParameterFilter.ParameterValue + '''

                 '

FROM #ParameterFilter



---------------------------------------------------------------------

/* --- Insert into Temp Events Table --- */

CREATE TABLE #Events 

(

		EventOriginId				uniqueidentifier not null	

		,ManagedEntityRowId			int not null

		,EventDisplayNumber			nvarchar(255) 	COLLATE database_default 

		,DateTime					nvarchar(255)	COLLATE database_default

		,RuleDefaultName			nvarchar(255) 	COLLATE database_default

		,EventLevelTitle			nvarchar(255) 	COLLATE database_default

		,EventChannelTitle			nvarchar(255) 	COLLATE database_default

		,EventCategoryTitle			nvarchar(255) 	COLLATE database_default

		,EventPublisherName			nvarchar(255) 	COLLATE database_default

		,ManagedEntityDefaultName	nvarchar(255) 	COLLATE database_default

		,ManagedEntityTypeDefaultName	nvarchar(255) COLLATE database_default

		,ManagementGroupDefaultName	nvarchar(255) 	COLLATE database_default

		,ComputerName				nvarchar(255) 	COLLATE database_default

		,UserName					nvarchar(255) 	COLLATE database_default

)



DECLARE @Query nvarchar(max)

SELECT @Query= 

	'SELECT 

	Event.vEvent.EventOriginId,

	vManagedEntity.ManagedEntityRowId,

	Event.vEvent.EventDisplayNumber,

	Event.vEvent.DateTime,

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

	vEventLevel.EventLevelTitle ,

	vEventChannel.EventChannelTitle ,

	vEventCategory.EventCategoryTitle,

	vEventPublisher.EventPublisherName ,

	vManagedEntity.ManagedEntityDefaultName,

	vManagedEntityType.ManagedEntityTypeDefaultName ,

	vManagementGroup.ManagementGroupDefaultName ,

	vEventLoggingComputer.ComputerName,

	vEventUserName.UserName 

	FROM 

	Event.vEvent 

	INNER JOIN

	Event.vEventRule ON Event.vEvent.EventOriginId = Event.vEventRule.EventOriginId

	INNER JOIN

	vRule ON vRule.RuleRowId = Event.vEventRule.RuleRowId 

	INNER JOIN

	vEventPublisher ON vEventPublisher.EventPublisherRowId = Event.vEvent.EventPublisherRowId 

	INNER JOIN

	vEventCategory ON vEventCategory.EventCategoryRowId = Event.vEvent.EventCategoryRowId 

	INNER JOIN

	vEventLevel ON vEventLevel.EventLevelId = Event.vEvent.EventLevelId 

	INNER JOIN

	vManagedEntity ON Event.vEventRule.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId  

	INNER JOIN 

	vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId 

	INNER JOIN

	vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId 

	INNER JOIN 

	vEventLoggingComputer ON vEventLoggingComputer.EventLoggingComputerRowId = Event.vEvent.LoggingComputerRowId

	INNER JOIN

	vEventUserName ON vEventUserName.EventUserNameRowId = Event.vEvent.UserNameRowId

	INNER JOIN 

	vEventChannel ON vEventChannel.EventChannelRowId = Event.vEvent.EventChannelRowId

	INNER JOIN

    #ObjectList ON vManagedEntity.ManagedEntityRowId = #ObjectList.ManagedEntityRowId  

    LEFT OUTER JOIN

    vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid 

    AND 

    vDisplayString.LanguageCode ='''+ @LanguageCode +''''+ 



    ISNULL(@ParameterFilterJoin, '')



	+	'WHERE

						(Event.vEvent.DateTime &gt;='''+ CONVERT(nvarchar(100), @StartDate, 126) +''') AND 

						(Event.vEvent.DateTime &lt; '''+ CONVERT(nvarchar(100), @EndDate, 126) +''' )'



	+	CASE 

			WHEN (NOT @FilterCount = 0)

			THEN ' AND ' + @FilterList

			ELSE ''

		END



  INSERT INTO #Events

  EXECUTE(@Query)



  SET @Error = @@ERROR

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



---------------------------------------------------------------------



  CREATE TABLE #SelectedSet 

  (

		EventOriginId uniqueidentifier not null	

		,PropertyID	nvarchar(100) COLLATE database_default

		,PropertyValue nvarchar(256) COLLATE database_default

		,[Position] int

  )



  DECLARE @EventID uniqueidentifier

  SELECT TOP(1) @EventID= #Events.EventOriginId FROM #Events



  SET @Error = @@ERROR

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



  INSERT #SelectedSet		

  EXECUTE('SELECT 

			 unpvt.EventOriginId

			,#ColumnPicker.PropertyID

			,PropValue

			,#ColumnPicker.Position

		FROM 

			#Events

		UNPIVOT

	    (

		 PropValue FOR PropertyID IN ('+ @ColumnList +')

		) AS unpvt 

			INNER JOIN 

			#ColumnPicker ON unpvt.PropertyID = #ColumnPicker.PropertyID

			WHERE #ColumnPicker.Visible = ''true''')



  SET @Error = @@ERROR

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



  SELECT #Events.EventOriginId

		,#Events.ManagedEntityRowId

		,#Events.EventDisplayNumber

		,#Events.EventPublisherName

		,#SelectedSet.PropertyID

		,#SelectedSet.PropertyValue

		,#SelectedSet.Position

  FROM	#Events INNER JOIN 

		#SelectedSet ON #SelectedSet.EventOriginId = #Events.EventOriginId



  UNION



  SELECT #Events.EventOriginId

		,#Events.ManagedEntityRowId

		,#Events.EventDisplayNumber

		,#Events.EventPublisherName

		,#ColumnPicker.PropertyID

		,Event.vEventParameter.ParameterValue

		,#ColumnPicker.Position

  FROM 

		Event.vEventParameter INNER JOIN 

		#Events ON Event.vEventParameter.EventOriginId = #Events.EventOriginId INNER JOIN 

		#ColumnPicker ON Event.vEventParameter.ParameterIndex = CAST(SUBSTRING(#ColumnPicker.PropertyID, LEN('Parameter') + 1, LEN(#ColumnPicker.PropertyID) - LEN('Parameter')) AS int)

  WHERE 

		#ColumnPicker.PropertyID like 'Parameter%' and #ColumnPicker.Visible = 'true'



  UNION

	

  SELECT #Events.EventOriginId

		,#Events.ManagedEntityRowId

		,#Events.EventDisplayNumber

		,#Events.EventPublisherName

		,Properties.PropertyID

		,NULL

		,Properties.Position

  FROM

		#Events  

		CROSS JOIN

			( SELECT PropertyID, #ColumnPicker.Position

			  FROM  #ColumnPicker

			  WHERE #ColumnPicker.PropertyID LIKE 'Parameter%' AND #ColumnPicker.PropertyID  NOT IN

												(

													SELECT 'Parameter' + CONVERT(nvarchar(100),Event.vEventParameter.ParameterIndex)

													FROM Event.vEventParameter 

													INNER JOIN #Events ON Event.vEventParameter.EventOriginId = #Events.EventOriginId

													WHERE #Events.EventOriginId = @EventID

												)

			 ) AS Properties 

  WHERE #Events.EventOriginId = @EventID



  SET @Error = @@ERROR

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



------------------------------------------------------------------------------------------------



  SET @Error = @@ERROR

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

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



QuitError:

  IF ((@Error = 0) AND (@ExecError &lt;&gt; 0)) SET @Error = @ExecError



/* Clean Up */

  -- remove xml document if opened

IF @xmldoc IS NOT NULL EXEC sp_xml_removedocument @xmldoc



DROP TABLE #SelectedSet

DROP TABLE #Events

DROP TABLE #ParameterFilter

DROP TABLE #ColumnPicker

DROP TABLE #ObjectList



RETURN @Error



END

GO

 

GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_EventTemplateReportDataGet] TO OpsMgrReader

GO













                </Upgrade>

</DataWarehouseScript>