Wednesday, February 15, 2012

Customized SSRS reporting for SCOM Alerts Breakdown

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.

SQL Query

Select
L1.server
,L1.ForestDNSName
,L1.FullName
,L1.AlertName
,L1.priority
,L1.severity
,sum(L1.severitynone) as 'Severity None Count'
,sum(L1.severitywarning) as 'Severity Warning Count'
,sum(L1.severityCritical) as 'Severity Critical Count'
,L1.triggermonth
,L1.triggeryear
from
(
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
else 0
end as 'SeverityWarning'
,case when (asy.Severity = 'Critical' and RepeatCount =0) then 1
when (Asy.Severity = 'Critical' and RepeatCount >0) then RepeatCount
else 0
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'
else 'Production'
end as ServerRole

            ,day(ars.DWCreatedDateTime) triggerdate
            ,month(ars.DWCreatedDateTime) triggermonth
            ,year(ars.DWCreatedDateTime) triggeryear
            , MTC.ForestDnsName
            ,MTC.OrganizationalUnit
            ,MTC.NetbiosComputerName as 'Server'
            ,ME.FullName

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))

)L1
Group by
L1.server
,L1.AlertName
,L1.priority
,L1.Severity
,L1.triggermonth
,L1.triggerdate
,L1.triggeryear
,L1.ForestDNSName
,L1.FullName

2 comments:

  1. I'm trying to get this to work on SCOM 2012 but I can't find a table or view named 'AlertPriority' or 'AlertSeverity' in any OpsMgr or the DW database. I'm even looking on SCOM 2007 and I don't see that. What is that? Or do you have an updated query for SCOM 2012?

    ReplyDelete
  2. The 2 tables 'AlertPriority" and 'AlertSeverity' are cretaed by myself.
    To create this tables use the below

    CREATE TABLE [dbo].[AlertPriority](
    [Alert] [int] NULL,
    [Priority] [varchar](12) NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[AlertSeverity](
    [Alert] [int] NULL,
    [Severity] [varchar](12) NULL
    ) ON [PRIMARY]

    The values for the 'AlertSeverity' are as below
    Alert Severity
    0 None
    1 Warning
    2 Critical

    and For AlertPriority is the below
    Alert Priority
    0 High
    1 Medium
    2 Low

    ReplyDelete