I was tasked to come up with something that will enable the breakdown of the SCOM alerts by the severity (Critical, Warning, Information) as well as Server Level.
The generic report that comes with SCOM reporting doesn’t seem to be able to provide what is required.
The report was created with the parameters of the year and month which the alerts are generated as well as select the group to query against (SCOM group which we usually use to group servers of a certain role , site etc)
I am using the below SQL query together with SQL Server Reporting Services (SSRS) .
For ease of use for all, I have uploaded the RDL to http://www.mediafire.com/?ve5zoueabbbhd
The output will be something similar to the below screenshot.
,sum(L1.severitynone) as 'Severity None Count'
,sum(L1.severitywarning) as 'Severity Warning Count'
,sum(L1.severityCritical) as 'Severity Critical Count'
select AlertName as 'Alertname'
,ars.DWCreatedDateTime as 'createdDateTime'
,apy.Priority as 'Priority'
,asy.severity as 'Severity'
,case when (asy.Severity = 'Warning' and RepeatCount =0) then 1
when (Asy.Severity = 'Warning' and RepeatCount >0) then RepeatCount
end as 'SeverityWarning'
,case when (asy.Severity = 'Critical' and RepeatCount =0) then 1
when (Asy.Severity = 'Critical' and RepeatCount >0) then RepeatCount
end as 'SeverityCritical'
‘This classifies the servers into Development, DR or Production based on OU info and NetbiosName
,case when (lower(MTC.OrganizationalUnit) like '%dev%' or lower(MTC.NetbiosComputerName) like '%dev%') then 'Development'
when (lower(MTC.OrganizationalUnit) like '%dr%' or lower(MTC.NetbiosComputerName) like '%dr%') then 'DR'
end as ServerRole
,MTC.NetbiosComputerName as 'Server'
from Alert.vAlertResolutionState ars
inner join alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
left join dbo.vManagedEntity ME on ME.ManagedEntityRowId = alt.ManagedEntityRowId
left join [OperationsManager].[dbo].[MT_Computer] MTC on lower(ME.path) like '%' + lower(MTC.NetbiosComputerName)+ '%'
left join alertpriority Apy on Apy.alert = alt.Priority
left join alertseverity Asy on Asy.alert = alt.severity
where month(ars.DWCreatedDateTime) in (@Month) and year(ars.DWCreatedDateTime) in (@YearDate) and ME.fullname NOT Like '%Jala%' and MTC.DNSName in (select TargetMonitoringObjectDisplayName as 'Group Members' from [OperationsManager].dbo.RelationshipGenericView where isDeleted=0 AND SourceMonitoringObjectDisplayName in (@Scomgrp))