SCOM DW DB에서 특정 클래스에서 발생한 경고 쿼리
심각도, 컴퓨터 이름, 클래스 ID/이름, 경고 이름, 발생 시간, 마지막 수정 시간(해결 시간), 소요 시간(분), 반복 횟수
Declare @Class nvarchar(30) = '%Operating System%' -- 클래스 이름 필터링
Declare @StartDate datetime = '2011-01-01' -- 시작 날짜
Declare @EndDate datetime = '2011-06-06' -- 종료 날짜
Select
Case
When Alert.vAlert.Severity = '0' Then N'Information'
When Alert.vAlert.Severity = '1' Then N'Warning'
When Alert.vAlert.Severity = '2' Then N'Critical'
End as [Severity],
Case
When ManagedEntity.Path is NULL Then ManagedEntity.Name
Else ManagedEntity.Path
End as [Path],
ManagedEntityTypeSystemName as [ClassID],
ManagedEntityTypeDefaultName as [ClassName],
AlertName as [AlertName],
Convert(Char(20),Dateadd(hh,9,RaisedDateTime),120) as [Raised],
Convert(Char(20),Dateadd(hh,9,DWLastModifiedDateTime),120) as [LastModified],
DATEDIFF(n,RaisedDateTime,DWLastModifiedDateTime) as [MinutesToResolve],
RepeatCount as [RepeatCount]
from Alert.vAlert
Join ManagedEntity on ManagedEntity.ManagedEntityRowId = Alert.vAlert.ManagedEntityRowId
Join ManagedEntityType on ManagedEntity.ManagedEntityTypeRowId = ManagedEntityType.ManagedEntityTypeRowId
Where ManagedEntityType.ManagedEntityTypeDefaultName like @Class AND
Dateadd(hh,9,RaisedDateTime)>= @StartDate AND Dateadd(hh,9,RaisedDateTime) <= DateAdd(dd,1,@EndDate) AND
ManagedEntityTypeSystemName <> 'Microsoft.SystemCenter.HealthServiceWatcher' AND -- HeartBeat 오류 제외
Severity IN (1,2) -- 심각도 필터링(0-정보, 1-경고, 2-위험)
Group By Severity,Path,ManagedEntity.Name,AlertName,RaisedDateTime,ManagedEntityTypeSystemName,RepeatCount,DWLastModifiedDateTime,ManagedEntityTypeDefaultName
Order by RaisedDateTime DESC
SCCM2007-SP2-KB2489044-KOR.msi_
