<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.MostCommonEvents" Accessibility="Public" DataWarehouseDataSet="DataWarehouse!Microsoft.SystemCenter.DataWarehouse.DataSet.Event"> <Install>
--===============================================================
-- DROP EXSITING PROCEDURE
--===============================================================
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportDataGet]
@StartDate datetime
,@EndDate datetime
,@Threshold int
,@ObjectList xml
,@EventPublisherXml xml = NULL
,@EventLevelXml xml = NULL
,@EventCategoryXml xml = NULL
,@EventChannelXml xml = NULL
,@LanguageCode varchar(3)= 'ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@ExecResult int
,@XmlDocHandle int
,@EventPublisherCount int
,@EventCategoryCount int
,@EventLevelCount int
,@EventChannelCount int
,@StandardDatasetTableMapRowId int
,@DatasetId uniqueidentifier
,@TableNameSuffix sysname
,@Statement nvarchar(max)
,@EventCount bigint
,@EventTotalCount bigint
,@PartitionTableNameSuffix varchar(50)
-- build a list of MEs we need to get events for
CREATE TABLE #ManagedEntity (
ManagedEntityRowId int NOT NULL
)
-- extract event publishers requested into temp table
CREATE TABLE #EventPublisher (
EventPublisherRowId int NOT NULL
)
IF (@EventPublisherXml IS NOT NULL)
BEGIN
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @EventPublisherXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'EventPublisher', @ExecResult)
INSERT #EventPublisher (
EventPublisherRowId
)
SELECT xml.EventPublisherRowId
FROM OPENXML(@XmlDocHandle, 'Data/EventPublishers/EventPublisher', 2) WITH (
EventPublisherRowId int '.') xml
IF (@XmlDocHandle IS NOT NULL)
BEGIN
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
END
END
SELECT @EventPublisherCount = COUNT(*)
FROM #EventPublisher
-- extract event categories requested into temp table
CREATE TABLE #EventCategory (
EventCategoryRowId int NOT NULL
)
IF (@EventCategoryXml IS NOT NULL)
BEGIN
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @EventCategoryXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'EventCategory', @ExecResult)
INSERT #EventCategory (EventCategoryRowId)
SELECT xml.EventCategoryRowId
FROM OPENXML(@XmlDocHandle, 'Data/EventCategories/EventCategory', 2) WITH (
EventCategoryRowId int '.') xml
IF (@XmlDocHandle IS NOT NULL)
BEGIN
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
END
END
SELECT @EventCategoryCount = COUNT(*)
FROM #EventCategory
-- extract event levels requested into temp table
CREATE TABLE #EventLevel (
EventLevelId int NOT NULL
)
IF (@EventLevelXml IS NOT NULL)
BEGIN
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @EventLevelXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'EventLevel', @ExecResult)
INSERT #EventLevel (EventLevelId)
SELECT xml.EventLevelId
FROM OPENXML(@XmlDocHandle, 'Data/EventLevels/EventLevel', 2) WITH (
EventLevelId int '.') xml
IF (@XmlDocHandle IS NOT NULL)
BEGIN
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
END
END
-- extract event channels requested into temp table
CREATE TABLE #EventChannel (
EventChannelRowId int NOT NULL
)
IF (@EventChannelXml IS NOT NULL)
BEGIN
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @EventChannelXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'EventChannel', @ExecResult)
INSERT #EventChannel (
EventChannelRowId
)
SELECT xml.EventChannelRowId
FROM OPENXML(@XmlDocHandle, 'Data/EventChannels/EventChannel', 2) WITH (
EventChannelRowId int '.') xml
IF (@XmlDocHandle IS NOT NULL)
BEGIN
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
END
END
SELECT @EventChannelCount = COUNT(*)
FROM #EventChannel
-- treat events with level 0 as informational (level 4)
-- sometimes MPs produce level 0 events
INSERT #EventLevel (EventLevelId)
SELECT 0
FROM #EventLevel
WHERE (EventLevelId = 4)
SELECT @EventLevelCount = COUNT(*)
FROM #EventLevel
-- extract events from each partition separately
-- into a temp table
-- IMPORTANT: we consider two events the same if
-- they are loged in the same log, have the same number,
-- source (publisher), level (info/warn/err) and category
-- AND was colelcted by the same rule targeting the same entity
CREATE TABLE #Event (
SampleEventOriginId uniqueidentifier NOT NULL
,SampleEventPartitionTableNameSuffix varchar(50) NOT NULL
,EventCount bigint NOT NULL
,ManagedEntityRowId int NOT NULL
,RuleRowId int NOT NULL
,EventDisplayNumber int NOT NULL
,EventChannelRowId int NOT NULL
,EventLevelId int NOT NULL
,EventPublisherRowId int NOT NULL
,EventCategoryRowId int NOT NULL
)
SET @StandardDatasetTableMapRowId = 0
-- Find event dataset id
SELECT @DatasetId = d.DatasetId
FROM vDataset d
JOIN vDatasetTypeManagementPackVersion dtmpv ON (d.DatasetTypeManagementPackVersionRowId = dtmpv.DatasetTypeManagementPackVersionRowId)
JOIN vDatasetType dt ON (dtmpv.DatasetTypeRowId = dt.DatasetTypeRowId)
WHERE (dt.DatasetTypeSystemName = 'Microsoft.SystemCenter.DataWarehouse.Dataset.Event')
-- roll through event partitions and grab
-- events matching criteria & their count
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (ISNULL(StartDateTime, '19000101') <= @EndDate)
AND (ISNULL(EndDateTime, '99991231') >= @StartDate)
)
BEGIN
SELECT TOP 1
@TableNameSuffix = TableNameSuffix
,@StandardDatasetTableMapRowId = StandardDatasetTableMapRowId
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (ISNULL(StartDateTime, '19000101') <= @EndDate)
AND (ISNULL(EndDateTime, '99991231') >= @StartDate)
ORDER BY StandardDatasetTableMapRowId
-- note: MAX can't work on GUIDs, so have to convert to varchar
SET @Statement =
' INSERT #Event ('
+ ' SampleEventOriginId'
+ ' ,SampleEventPartitionTableNameSuffix'
+ ' ,EventCount'
+ ' ,ManagedEntityRowId'
+ ' ,RuleRowId'
+ ' ,EventDisplayNumber'
+ ' ,EventChannelRowId'
+ ' ,EventLevelId'
+ ' ,EventPublisherRowId'
+ ' ,EventCategoryRowId'
+ ' )'
+ ' SELECT '
+ ' MAX(CAST(e.EventOriginId AS varchar(100)))'
+ ' ,''' + @TableNameSuffix + ''''
+ ' ,COUNT(*)'
+ ' ,er.ManagedEntityRowId'
+ ' ,er.RuleRowId'
+ ' ,e.EventDisplayNumber'
+ ' ,e.EventChannelRowId'
+ ' ,e.EventLevelId'
+ ' ,e.EventPublisherRowId'
+ ' ,e.EventCategoryRowId'
+ ' FROM Event.Event_' + @TableNameSuffix + ' e'
+ ' JOIN Event.EventRule_' + @TableNameSuffix + ' er ON (e.EventOriginId = er.EventOriginId)'
+ ' JOIN #ManagedEntity me ON (er.ManagedEntityRowId = me.ManagedEntityRowId)'
-- add optional conditions
IF (@EventPublisherCount > 0)
BEGIN
SET @Statement = @Statement
+ ' JOIN #EventPublisher ep ON (e.EventPublisherRowId = ep.EventPublisherRowId)'
END
IF (@EventCategoryCount > 0)
BEGIN
SET @Statement = @Statement
+ ' JOIN #EventCategory ec ON (e.EventCategoryRowId = ec.EventCategoryRowId)'
END
IF (@EventChannelCount > 0)
BEGIN
SET @Statement = @Statement
+ ' JOIN #EventChannel ech ON (e.EventChannelRowId = ech.EventChannelRowId)'
END
IF (@EventLevelCount > 0)
BEGIN
SET @Statement = @Statement
+ ' JOIN #EventLevel el ON (e.EventLevelId = el.EventLevelId)'
END
SET @Statement = @Statement
+ ' WHERE (e.[DateTime] BETWEEN ''' + CONVERT(varchar(30), ISNULL(@StartDate, '19000101'), 120)+ ''' AND ''' + CONVERT(varchar(30), ISNULL(@EndDate, '99991231'), 120) + ''')'
+ ' GROUP BY'
+ ' er.ManagedEntityRowId'
+ ' ,er.RuleRowId'
+ ' ,e.EventDisplayNumber'
+ ' ,e.EventChannelRowId'
+ ' ,e.EventLevelId'
+ ' ,e.EventPublisherRowId'
+ ' ,e.EventCategoryRowId'
EXECUTE (@Statement)
END
-- calculate total number of events collected
SELECT @EventTotalCount = SUM(EventCount)
FROM #Event
-- treat event level 0 as info
-- many MPs set it to 0 instead of 4
UPDATE #Event
SET EventLevelId = 4
WHERE EventLevelId = 0
-- note: all fields are nullable to
-- allow for "other events" counter
CREATE TABLE #EventFinal (
SummaryRowInd tinyint NOT NULL DEFAULT (0)
,SampleEventOriginId uniqueidentifier NULL
,SampleEventPartitionTableNameSuffix varchar(50) NULL
,SampleEventRawDescription nvarchar(max) NULL
,SampleEventRenderedDescription nvarchar(max) NULL
,EventCount bigint NOT NULL
,ManagedEntityRowId int NULL
,RuleRowId int NULL
,EventDisplayNumber int NULL
,EventChannelRowId int NULL
,EventLevelId int NULL
,EventPublisherRowId int NULL
,EventCategoryRowId int NULL
)
-- first figure out which TOP (@Threshold)
-- events we will have across all obejcts and rules
INSERT #EventFinal (
SummaryRowInd
,EventCount
,EventDisplayNumber
,EventChannelRowId
,EventLevelId
,EventPublisherRowId
,EventCategoryRowId
)
SELECT TOP (@Threshold)
2
,SUM(EventCount)
,EventDisplayNumber
,EventChannelRowId
,EventLevelId
,EventPublisherRowId
,EventCategoryRowId
FROM #Event
GROUP BY
EventDisplayNumber
,EventChannelRowId
,EventLevelId
,EventPublisherRowId
,EventCategoryRowId
ORDER BY SUM(EventCount) DESC
SELECT @EventCount = SUM(EventCount)
FROM #EventFinal
-- add row for "other events" before
-- calculating row numbers for sumamry events
IF (@EventCount IS NOT NULL)
BEGIN
INSERT #EventFinal (SummaryRowInd, EventCount)
VALUES (1, @EventTotalCount - @EventCount)
END
-- insert break-down by ME and Rule Id for the events
-- that made it into TOP(N)
INSERT #EventFinal (
SummaryRowInd
,SampleEventOriginId
,SampleEventPartitionTableNameSuffix
,EventCount
,ManagedEntityRowId
,RuleRowId
,EventDisplayNumber
,EventChannelRowId
,EventLevelId
,EventPublisherRowId
,EventCategoryRowId
)
SELECT
0
,CAST(RIGHT(MAX(e.SampleEventPartitionTableNameSuffix + CAST(e.SampleEventOriginId AS varchar(100))), 36) AS uniqueidentifier)
,LEFT(MAX(e.SampleEventPartitionTableNameSuffix + CAST(e.SampleEventOriginId AS varchar(100))), 32)
,SUM(e.EventCount)
,e.ManagedEntityRowId
,e.RuleRowId
,ef.EventDisplayNumber
,ef.EventChannelRowId
,ef.EventLevelId
,ef.EventPublisherRowId
,ef.EventCategoryRowId
FROM #Event e
JOIN #EventFinal ef ON (e.EventDisplayNumber = ef.EventDisplayNumber) AND
(e.EventChannelRowId = ef.EventChannelRowId) AND
(e.EventPublisherRowId = ef.EventPublisherRowId) AND
(e.EventCategoryRowId = ef.EventCategoryRowId) AND
(e.EventLevelId = ef.EventLevelId)
GROUP BY
e.ManagedEntityRowId
,e.RuleRowId
,ef.EventDisplayNumber
,ef.EventChannelRowId
,ef.EventLevelId
,ef.EventPublisherRowId
,ef.EventCategoryRowId
-- roll through the list of sample event partitions
-- and obtain sample event raw & rendered description
DECLARE @Partition TABLE (
PartitionTableNameSuffix varchar(50) NOT NULL
)
INSERT @Partition (PartitionTableNameSuffix)
SELECT DISTINCT SampleEventPartitionTableNameSuffix
FROM #EventFinal
WHERE (SampleEventPartitionTableNameSuffix IS NOT NULL) -- exclude summary rows
SELECT @PartitionTableNameSuffix = ''
WHILE EXISTS (SELECT * FROM @Partition WHERE PartitionTableNameSuffix > @PartitionTableNameSuffix)
BEGIN
SELECT TOP 1
@PartitionTableNameSuffix = PartitionTableNameSuffix
FROM @Partition
WHERE PartitionTableNameSuffix > @PartitionTableNameSuffix
ORDER BY PartitionTableNameSuffix
-- join to string data for presentation
SELECT
e.SummaryRowInd
,SummaryRowNumber = ROW_NUMBER() OVER(ORDER BY SummaryRowInd DESC, EventCount DESC)
,e.SampleEventOriginId
,e.SampleEventRawDescription
,e.SampleEventRenderedDescription
,e.EventCount
,PercentOfTopEvents =
CASE
WHEN e.EventDisplayNumber IS NULL THEN NULL -- return null for 'all events' line since they are not part of 'top events'
ELSE e.EventCount * 1.0 / @EventCount * 100
END
,PercentOfTotalEvents = e.EventCount * 1.0 / @EventTotalCount * 100
,me.ManagedEntityGuid
,me.ManagedEntityDefaultName
,ManagedEntityPath = me.Path
,mg.ManagementGroupDefaultName
,mg.ManagementGroupGuid
,met.ManagedEntityTypeGuid
,ManagedEntityTypeDisplayName = ISNULL(ds_met.Name, met.ManagedEntityTypeDefaultName)
,ManagedEntityTypeImage = meti.[Image]
,r.RuleGuid
,RuleDisplayName = ISNULL(ds_r.Name, r.RuleDefaultName)
,RuleManagementPackDisplayName = ISNULL(ds_mp.Name, mp.ManagementPackDefaultName)
,e.EventDisplayNumber
,ech.EventChannelTitle
,e.EventLevelId
,ep.EventPublisherName
,ec.EventCategoryTitle
,el.EventLevelTitle
FROM #EventFinal e
LEFT JOIN vManagedEntity me ON (e.ManagedEntityRowId = me.ManagedEntityRowId)
LEFT JOIN vManagementGroup mg ON (me.ManagementGroupRowId = mg.ManagementGroupRowId)
LEFT JOIN vManagedEntityType met ON (me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
LEFT JOIN vRule r ON (e.RuleRowId = r.RuleRowId)
LEFT JOIN vEventChannel ech ON (e.EventChannelRowId = ech.EventChannelRowId)
LEFT JOIN vEventPublisher ep ON (e.EventPublisherRowId = ep.EventPublisherRowId)
LEFT JOIN vEventCategory ec ON (e.EventCategoryRowId = ec.EventCategoryRowId)
LEFT JOIN vManagementPack mp ON (mp.ManagementPackRowId = r.ManagementPackRowId)
LEFT JOIN vManagedEntityTypeImage meti ON (meti.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId) AND (meti.ImageCategory ='u16x16Icon')
LEFT JOIN vEventLevel el ON (e.EventLevelId = el.EventLevelId)
LEFT JOIN vDisplayString ds_met ON (ds_met.ElementGuid = met.ManagedEntityTypeGuid) AND (ds_met.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString ds_r ON (ds_r.ElementGuid = r.RuleGuid) AND (ds_r.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString ds_mp ON (ds_mp.ElementGuid = mp.ManagementPackVersionIndependentGuid) AND (ds_mp.LanguageCode = @LanguageCode)
ORDER BY SummaryRowInd DESC, EventCount DESC
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- cleanup
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#ManagedEntity') IS NOT NULL)
DROP TABLE #ManagedEntity
IF (OBJECT_ID('tempdb..#EventPublisher') IS NOT NULL)
DROP TABLE #EventPublisher
IF (OBJECT_ID('tempdb..#EventCategory') IS NOT NULL)
DROP TABLE #EventCategory
IF (OBJECT_ID('tempdb..#EventChannel') IS NOT NULL)
DROP TABLE #EventChannel
IF (OBJECT_ID('tempdb..#EventLevel') IS NOT NULL)
DROP TABLE #EventLevel
IF (OBJECT_ID('tempdb..#Event') IS NOT NULL)
DROP TABLE #Event
IF (OBJECT_ID('tempdb..#EventFinal') IS NOT NULL)
DROP TABLE #EventFinal
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportDataGet] TO OpsMgrReader
GO </Install> <Uninstall>
EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportDataGet]
END
GO
</Uninstall> <Upgrade>
--===============================================================
-- DROP EXSITING PROCEDURE
--===============================================================
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportDataGet]
@StartDate datetime
,@EndDate datetime
,@Threshold int
,@ObjectList xml
,@EventPublisherXml xml = NULL
,@EventLevelXml xml = NULL
,@EventCategoryXml xml = NULL
,@EventChannelXml xml = NULL
,@LanguageCode varchar(3)= 'ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@ExecResult int
,@XmlDocHandle int
,@EventPublisherCount int
,@EventCategoryCount int
,@EventLevelCount int
,@EventChannelCount int
,@StandardDatasetTableMapRowId int
,@DatasetId uniqueidentifier
,@TableNameSuffix sysname
,@Statement nvarchar(max)
,@EventCount bigint
,@EventTotalCount bigint
,@PartitionTableNameSuffix varchar(50)
-- build a list of MEs we need to get events for
CREATE TABLE #ManagedEntity (
ManagedEntityRowId int NOT NULL
)
-- extract event publishers requested into temp table
CREATE TABLE #EventPublisher (
EventPublisherRowId int NOT NULL
)
IF (@EventPublisherXml IS NOT NULL)
BEGIN
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @EventPublisherXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'EventPublisher', @ExecResult)
INSERT #EventPublisher (
EventPublisherRowId
)
SELECT xml.EventPublisherRowId
FROM OPENXML(@XmlDocHandle, 'Data/EventPublishers/EventPublisher', 2) WITH (
EventPublisherRowId int '.') xml
IF (@XmlDocHandle IS NOT NULL)
BEGIN
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
END
END
SELECT @EventPublisherCount = COUNT(*)
FROM #EventPublisher
-- extract event categories requested into temp table
CREATE TABLE #EventCategory (
EventCategoryRowId int NOT NULL
)
IF (@EventCategoryXml IS NOT NULL)
BEGIN
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @EventCategoryXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'EventCategory', @ExecResult)
INSERT #EventCategory (EventCategoryRowId)
SELECT xml.EventCategoryRowId
FROM OPENXML(@XmlDocHandle, 'Data/EventCategories/EventCategory', 2) WITH (
EventCategoryRowId int '.') xml
IF (@XmlDocHandle IS NOT NULL)
BEGIN
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
END
END
SELECT @EventCategoryCount = COUNT(*)
FROM #EventCategory
-- extract event levels requested into temp table
CREATE TABLE #EventLevel (
EventLevelId int NOT NULL
)
IF (@EventLevelXml IS NOT NULL)
BEGIN
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @EventLevelXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'EventLevel', @ExecResult)
INSERT #EventLevel (EventLevelId)
SELECT xml.EventLevelId
FROM OPENXML(@XmlDocHandle, 'Data/EventLevels/EventLevel', 2) WITH (
EventLevelId int '.') xml
IF (@XmlDocHandle IS NOT NULL)
BEGIN
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
END
END
-- extract event channels requested into temp table
CREATE TABLE #EventChannel (
EventChannelRowId int NOT NULL
)
IF (@EventChannelXml IS NOT NULL)
BEGIN
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @EventChannelXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'EventChannel', @ExecResult)
INSERT #EventChannel (
EventChannelRowId
)
SELECT xml.EventChannelRowId
FROM OPENXML(@XmlDocHandle, 'Data/EventChannels/EventChannel', 2) WITH (
EventChannelRowId int '.') xml
IF (@XmlDocHandle IS NOT NULL)
BEGIN
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
END
END
SELECT @EventChannelCount = COUNT(*)
FROM #EventChannel
-- treat events with level 0 as informational (level 4)
-- sometimes MPs produce level 0 events
INSERT #EventLevel (EventLevelId)
SELECT 0
FROM #EventLevel
WHERE (EventLevelId = 4)
SELECT @EventLevelCount = COUNT(*)
FROM #EventLevel
-- extract events from each partition separately
-- into a temp table
-- IMPORTANT: we consider two events the same if
-- they are loged in the same log, have the same number,
-- source (publisher), level (info/warn/err) and category
-- AND was colelcted by the same rule targeting the same entity
CREATE TABLE #Event (
SampleEventOriginId uniqueidentifier NOT NULL
,SampleEventPartitionTableNameSuffix varchar(50) NOT NULL
,EventCount bigint NOT NULL
,ManagedEntityRowId int NOT NULL
,RuleRowId int NOT NULL
,EventDisplayNumber int NOT NULL
,EventChannelRowId int NOT NULL
,EventLevelId int NOT NULL
,EventPublisherRowId int NOT NULL
,EventCategoryRowId int NOT NULL
)
SET @StandardDatasetTableMapRowId = 0
-- Find event dataset id
SELECT @DatasetId = d.DatasetId
FROM vDataset d
JOIN vDatasetTypeManagementPackVersion dtmpv ON (d.DatasetTypeManagementPackVersionRowId = dtmpv.DatasetTypeManagementPackVersionRowId)
JOIN vDatasetType dt ON (dtmpv.DatasetTypeRowId = dt.DatasetTypeRowId)
WHERE (dt.DatasetTypeSystemName = 'Microsoft.SystemCenter.DataWarehouse.Dataset.Event')
-- roll through event partitions and grab
-- events matching criteria & their count
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (ISNULL(StartDateTime, '19000101') <= @EndDate)
AND (ISNULL(EndDateTime, '99991231') >= @StartDate)
)
BEGIN
SELECT TOP 1
@TableNameSuffix = TableNameSuffix
,@StandardDatasetTableMapRowId = StandardDatasetTableMapRowId
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (ISNULL(StartDateTime, '19000101') <= @EndDate)
AND (ISNULL(EndDateTime, '99991231') >= @StartDate)
ORDER BY StandardDatasetTableMapRowId
-- note: MAX can't work on GUIDs, so have to convert to varchar
SET @Statement =
' INSERT #Event ('
+ ' SampleEventOriginId'
+ ' ,SampleEventPartitionTableNameSuffix'
+ ' ,EventCount'
+ ' ,ManagedEntityRowId'
+ ' ,RuleRowId'
+ ' ,EventDisplayNumber'
+ ' ,EventChannelRowId'
+ ' ,EventLevelId'
+ ' ,EventPublisherRowId'
+ ' ,EventCategoryRowId'
+ ' )'
+ ' SELECT '
+ ' MAX(CAST(e.EventOriginId AS varchar(100)))'
+ ' ,''' + @TableNameSuffix + ''''
+ ' ,COUNT(*)'
+ ' ,er.ManagedEntityRowId'
+ ' ,er.RuleRowId'
+ ' ,e.EventDisplayNumber'
+ ' ,e.EventChannelRowId'
+ ' ,e.EventLevelId'
+ ' ,e.EventPublisherRowId'
+ ' ,e.EventCategoryRowId'
+ ' FROM Event.Event_' + @TableNameSuffix + ' e'
+ ' JOIN Event.EventRule_' + @TableNameSuffix + ' er ON (e.EventOriginId = er.EventOriginId)'
+ ' JOIN #ManagedEntity me ON (er.ManagedEntityRowId = me.ManagedEntityRowId)'
-- add optional conditions
IF (@EventPublisherCount > 0)
BEGIN
SET @Statement = @Statement
+ ' JOIN #EventPublisher ep ON (e.EventPublisherRowId = ep.EventPublisherRowId)'
END
IF (@EventCategoryCount > 0)
BEGIN
SET @Statement = @Statement
+ ' JOIN #EventCategory ec ON (e.EventCategoryRowId = ec.EventCategoryRowId)'
END
IF (@EventChannelCount > 0)
BEGIN
SET @Statement = @Statement
+ ' JOIN #EventChannel ech ON (e.EventChannelRowId = ech.EventChannelRowId)'
END
IF (@EventLevelCount > 0)
BEGIN
SET @Statement = @Statement
+ ' JOIN #EventLevel el ON (e.EventLevelId = el.EventLevelId)'
END
SET @Statement = @Statement
+ ' WHERE (e.[DateTime] BETWEEN ''' + CONVERT(varchar(30), ISNULL(@StartDate, '19000101'), 120)+ ''' AND ''' + CONVERT(varchar(30), ISNULL(@EndDate, '99991231'), 120) + ''')'
+ ' GROUP BY'
+ ' er.ManagedEntityRowId'
+ ' ,er.RuleRowId'
+ ' ,e.EventDisplayNumber'
+ ' ,e.EventChannelRowId'
+ ' ,e.EventLevelId'
+ ' ,e.EventPublisherRowId'
+ ' ,e.EventCategoryRowId'
EXECUTE (@Statement)
END
-- calculate total number of events collected
SELECT @EventTotalCount = SUM(EventCount)
FROM #Event
-- treat event level 0 as info
-- many MPs set it to 0 instead of 4
UPDATE #Event
SET EventLevelId = 4
WHERE EventLevelId = 0
-- note: all fields are nullable to
-- allow for "other events" counter
CREATE TABLE #EventFinal (
SummaryRowInd tinyint NOT NULL DEFAULT (0)
,SampleEventOriginId uniqueidentifier NULL
,SampleEventPartitionTableNameSuffix varchar(50) NULL
,SampleEventRawDescription nvarchar(max) NULL
,SampleEventRenderedDescription nvarchar(max) NULL
,EventCount bigint NOT NULL
,ManagedEntityRowId int NULL
,RuleRowId int NULL
,EventDisplayNumber int NULL
,EventChannelRowId int NULL
,EventLevelId int NULL
,EventPublisherRowId int NULL
,EventCategoryRowId int NULL
)
-- first figure out which TOP (@Threshold)
-- events we will have across all obejcts and rules
INSERT #EventFinal (
SummaryRowInd
,EventCount
,EventDisplayNumber
,EventChannelRowId
,EventLevelId
,EventPublisherRowId
,EventCategoryRowId
)
SELECT TOP (@Threshold)
2
,SUM(EventCount)
,EventDisplayNumber
,EventChannelRowId
,EventLevelId
,EventPublisherRowId
,EventCategoryRowId
FROM #Event
GROUP BY
EventDisplayNumber
,EventChannelRowId
,EventLevelId
,EventPublisherRowId
,EventCategoryRowId
ORDER BY SUM(EventCount) DESC
SELECT @EventCount = SUM(EventCount)
FROM #EventFinal
-- add row for "other events" before
-- calculating row numbers for sumamry events
IF (@EventCount IS NOT NULL)
BEGIN
INSERT #EventFinal (SummaryRowInd, EventCount)
VALUES (1, @EventTotalCount - @EventCount)
END
-- insert break-down by ME and Rule Id for the events
-- that made it into TOP(N)
INSERT #EventFinal (
SummaryRowInd
,SampleEventOriginId
,SampleEventPartitionTableNameSuffix
,EventCount
,ManagedEntityRowId
,RuleRowId
,EventDisplayNumber
,EventChannelRowId
,EventLevelId
,EventPublisherRowId
,EventCategoryRowId
)
SELECT
0
,CAST(RIGHT(MAX(e.SampleEventPartitionTableNameSuffix + CAST(e.SampleEventOriginId AS varchar(100))), 36) AS uniqueidentifier)
,LEFT(MAX(e.SampleEventPartitionTableNameSuffix + CAST(e.SampleEventOriginId AS varchar(100))), 32)
,SUM(e.EventCount)
,e.ManagedEntityRowId
,e.RuleRowId
,ef.EventDisplayNumber
,ef.EventChannelRowId
,ef.EventLevelId
,ef.EventPublisherRowId
,ef.EventCategoryRowId
FROM #Event e
JOIN #EventFinal ef ON (e.EventDisplayNumber = ef.EventDisplayNumber) AND
(e.EventChannelRowId = ef.EventChannelRowId) AND
(e.EventPublisherRowId = ef.EventPublisherRowId) AND
(e.EventCategoryRowId = ef.EventCategoryRowId) AND
(e.EventLevelId = ef.EventLevelId)
GROUP BY
e.ManagedEntityRowId
,e.RuleRowId
,ef.EventDisplayNumber
,ef.EventChannelRowId
,ef.EventLevelId
,ef.EventPublisherRowId
,ef.EventCategoryRowId
-- roll through the list of sample event partitions
-- and obtain sample event raw & rendered description
DECLARE @Partition TABLE (
PartitionTableNameSuffix varchar(50) NOT NULL
)
INSERT @Partition (PartitionTableNameSuffix)
SELECT DISTINCT SampleEventPartitionTableNameSuffix
FROM #EventFinal
WHERE (SampleEventPartitionTableNameSuffix IS NOT NULL) -- exclude summary rows
SELECT @PartitionTableNameSuffix = ''
WHILE EXISTS (SELECT * FROM @Partition WHERE PartitionTableNameSuffix > @PartitionTableNameSuffix)
BEGIN
SELECT TOP 1
@PartitionTableNameSuffix = PartitionTableNameSuffix
FROM @Partition
WHERE PartitionTableNameSuffix > @PartitionTableNameSuffix
ORDER BY PartitionTableNameSuffix
-- join to string data for presentation
SELECT
e.SummaryRowInd
,SummaryRowNumber = ROW_NUMBER() OVER(ORDER BY SummaryRowInd DESC, EventCount DESC)
,e.SampleEventOriginId
,e.SampleEventRawDescription
,e.SampleEventRenderedDescription
,e.EventCount
,PercentOfTopEvents =
CASE
WHEN e.EventDisplayNumber IS NULL THEN NULL -- return null for 'all events' line since they are not part of 'top events'
ELSE e.EventCount * 1.0 / @EventCount * 100
END
,PercentOfTotalEvents = e.EventCount * 1.0 / @EventTotalCount * 100
,me.ManagedEntityGuid
,me.ManagedEntityDefaultName
,ManagedEntityPath = me.Path
,mg.ManagementGroupDefaultName
,mg.ManagementGroupGuid
,met.ManagedEntityTypeGuid
,ManagedEntityTypeDisplayName = ISNULL(ds_met.Name, met.ManagedEntityTypeDefaultName)
,ManagedEntityTypeImage = meti.[Image]
,r.RuleGuid
,RuleDisplayName = ISNULL(ds_r.Name, r.RuleDefaultName)
,RuleManagementPackDisplayName = ISNULL(ds_mp.Name, mp.ManagementPackDefaultName)
,e.EventDisplayNumber
,ech.EventChannelTitle
,e.EventLevelId
,ep.EventPublisherName
,ec.EventCategoryTitle
,el.EventLevelTitle
FROM #EventFinal e
LEFT JOIN vManagedEntity me ON (e.ManagedEntityRowId = me.ManagedEntityRowId)
LEFT JOIN vManagementGroup mg ON (me.ManagementGroupRowId = mg.ManagementGroupRowId)
LEFT JOIN vManagedEntityType met ON (me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
LEFT JOIN vRule r ON (e.RuleRowId = r.RuleRowId)
LEFT JOIN vEventChannel ech ON (e.EventChannelRowId = ech.EventChannelRowId)
LEFT JOIN vEventPublisher ep ON (e.EventPublisherRowId = ep.EventPublisherRowId)
LEFT JOIN vEventCategory ec ON (e.EventCategoryRowId = ec.EventCategoryRowId)
LEFT JOIN vManagementPack mp ON (mp.ManagementPackRowId = r.ManagementPackRowId)
LEFT JOIN vManagedEntityTypeImage meti ON (meti.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId) AND (meti.ImageCategory ='u16x16Icon')
LEFT JOIN vEventLevel el ON (e.EventLevelId = el.EventLevelId)
LEFT JOIN vDisplayString ds_met ON (ds_met.ElementGuid = met.ManagedEntityTypeGuid) AND (ds_met.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString ds_r ON (ds_r.ElementGuid = r.RuleGuid) AND (ds_r.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString ds_mp ON (ds_mp.ElementGuid = mp.ManagementPackVersionIndependentGuid) AND (ds_mp.LanguageCode = @LanguageCode)
ORDER BY SummaryRowInd DESC, EventCount DESC
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- cleanup
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#ManagedEntity') IS NOT NULL)
DROP TABLE #ManagedEntity
IF (OBJECT_ID('tempdb..#EventPublisher') IS NOT NULL)
DROP TABLE #EventPublisher
IF (OBJECT_ID('tempdb..#EventCategory') IS NOT NULL)
DROP TABLE #EventCategory
IF (OBJECT_ID('tempdb..#EventChannel') IS NOT NULL)
DROP TABLE #EventChannel
IF (OBJECT_ID('tempdb..#EventLevel') IS NOT NULL)
DROP TABLE #EventLevel
IF (OBJECT_ID('tempdb..#Event') IS NOT NULL)
DROP TABLE #Event
IF (OBJECT_ID('tempdb..#EventFinal') IS NOT NULL)
DROP TABLE #EventFinal
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_MostCommonEventsReportDataGet] TO OpsMgrReader
GO </Upgrade>
</DataWarehouseScript>
Home
