<DataWarehouseScript ID="Microsoft.Windows.Server.AD.Class.Library.Report.Script.EventAnalysis" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_ActiveDirectory_Report_Library_GetEventAnalysisReportData')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_ActiveDirectory_Report_Library_GetEventAnalysisReportData] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_ActiveDirectory_Report_Library_GetEventAnalysisReportData]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@EventList xml,
@SelectedType nvarchar(255),
@SelectedCategory nvarchar(255),
@SelectedSource nvarchar(255),
@LanguageCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 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,
vRule.RuleDefaultName,
vEventLevel.EventLevelTitle,
vEventCategory.EventCategoryTitle,
vEventPublisher.EventPublisherName,
Event.vEventDetail.RenderedDescription,
vManagedEntity.ManagedEntityDefaultName,
vManagedEntity.ManagedEntityGuid,
vManagedEntity.Path,
vManagedEntityType.ManagedEntityTypeDefaultName,
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 >= @StartDate) AND (Event.vEvent.DateTime < @EndDate)
AND
( Event.vEvent.EventDisplayNumber in (Select * from #EventList))
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'
/* --------------------------------------- */
SET @Error = @@ERROR
QuitError:
DROP TABLE #ObjectList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_ActiveDirectory_Report_Library_GetEventAnalysisReportData] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_ActiveDirectory_Report_Library_GetEventAnalysisReportData')
BEGIN
DROP PROCEDURE dbo.[Microsoft_ActiveDirectory_Report_Library_GetEventAnalysisReportData]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_ActiveDirectory_Report_Library_GetEventAnalysisReportData')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_ActiveDirectory_Report_Library_GetEventAnalysisReportData] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_ActiveDirectory_Report_Library_GetEventAnalysisReportData]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@EventList xml,
@SelectedType nvarchar(255),
@SelectedCategory nvarchar(255),
@SelectedSource nvarchar(255),
@LanguageCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Home