SCOM – Alert Report To Target Specific Servers and Groups

I recently received a request for a daily report to show “Login Failed:” alerts for SQL servers in the environment.  I thought…no problem, we have an “Alerts” report out of box that should meet this requirement quite nicely.  Unfortunately, it does not.  The report is very handy for most circumstances, but it does not provide the functionality to specify specific alerts for a server/group.  There are some other ways to do this (Advanced Alert Search, query, view, etc.), but in this case the customer wanted a scheduled daily report, so it’s time for some report development!

To start, I need to figure out the SQL query to pull the requested data.  Knowing that the out of box report provides most of what we are looking for, I decided to reverse engineer the query used by the report.  To do this, I need to capture the query being used while the report is being executed using SQL Server Profiler on the SQL server hosting the SCOM Data Warehouse.  I will not dig into the steps to run the trace in this post, but you can follow these steps by clicking the link below…

https://scomanswers.wordpress.com/2015/03/08/scom-use-sql-server-profiler-to-capture-sql-queries-used-by-the-scom-console/

After running the SQL Server Profiler trace while executing the “Alerts” report, I can see that the report is executing a stored procedure called “Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet”.

ProfilerTrace

Next step, I need to locate the stored procedure in SQL Server Management Studio….

1.  To locate the sproc, open SQL Server Management Studio and connect to the SQL server hosting the OperationsManagerDW

2.  Under “Databases”, select OperationsManagerDW

3.  Expand the OperationsManagerDW, Programmability, and locate the “Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet” sproc.

The base query I am looking for is in the highlighted section of the screenshot below…

sproc

As you can see, there are some configuration changes that need to be made, but I have the meat of what I need. Next step, I need to develop the rest of the query.  I’ve made a few changes to the query to meet the customers requirements, in addition to some added parameters which I am using in BIDS to add search and drop down functionality in the report.  These variables can easily be removed and replaced with static values.

select vme2.Displayname,
vpd.Alertname,
vpd.RaisedDateTime,
MIN(vpd.AlertDescription) AS AlertDescription,
COUNT(*) AS RepeatCount
FROM
Alert.vAlert vpd
inner join vManagedEntity vme on vme.ManagedEntityRowId=vpd.ManagedEntityRowId
inner join vManagedEntity vme2 on vme2.ManagedEntityRowId=vme.TopLevelHostManagedEntityRowId
Where vpd.AlertName in (@Alert)
and vpd.RaisedDateTime between @StartDate and @EndDate
and vme2.ManagedEntityRowId in (@Computers)
Group by RaisedDateTime, vme2.Displayname, AlertName,  AlertDescription, RepeatCount

TIP:  If you want to schedule the report you can simply hardcode the @StartDate and @EndDate parameters or replace them with something like “vpd.RaisedDateTime >=DateDiff(“d”,-7,GetUTCDate)” which will give you the last week of data.

UPDATE 4/6/15:  I have been asked several times how to convert the report from GMT to local time.  This should do the trick!  The sections in bold are the lines that have been changed.  I have also added logic to enable the selecting of multiple alerts for the targeted server or group.

select vme2.Displayname,
vpd.Alertname,
 DATEADD(HOUR,-DATEDIFF(HOUR,GETDATE(),GetUTCDate()),vpd.RaisedDateTime) as RaisedDateTime,
MIN(vpd.AlertDescription) AS AlertDescription,
COUNT(*) AS RepeatCount
FROM
Alert.vAlert vpd
inner join vManagedEntity vme on vme.ManagedEntityRowId=vpd.ManagedEntityRowId
inner join vManagedEntity vme2 on vme2.ManagedEntityRowId=vme.TopLevelHostManagedEntityRowId
Where vpd.AlertName in (@Alert)
and DATEADD(HOUR,-DATEDIFF(HOUR,GETDATE(),GetUTCDate()),vpd.RaisedDateTime) between @StartDate and @EndDate
and vme2.ManagedEntityRowId in (@Computers)
Group by RaisedDateTime, vme2.Displayname, AlertName,  AlertDescription, RepeatCount
Order by RaisedDateTime

Final Report:

Alert Report

Alert Report Output

Management Pack:

NOTE: This management pack is for sample use only and should be tested extensively in a non-production environment – use at your own risk

Access the SCOM Reports management pack file: here

This posting is provided “AS IS” with no warranties.

Advertisements

5 thoughts on “SCOM – Alert Report To Target Specific Servers and Groups

  1. Hi Shawn,

    Thanks for great post. Would you be able to share the working xml via email, as I am not able to use the code provided in blog?

    Thank you.

    Like

  2. Hi Shawn,

    Thanks for sharing the MP. However we could not see SCOM reports showing up in the SCOM console after imported the MP. Is there anything else needs to be configured?

    Thanks

    Like

    • Hello,

      It takes a few minutes to populate, but I just verified in my new lab that it does import and function correctly. You may want to check the Operations Manager log to see if an error occurred, and verify whether it populated in SSRS.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s