Cameron Fuller
Cameron is an author, speaker, Microsoft MVP (Cloud and Datacenter Management), principal consultant and Corporate Practice Lead for Catapult Systems. Cameron blogs about infrastructure topics including SCOM, the Cloud, virtualization and consulting.

OpsMgr (#SCOM) Queries and Dashboards for Alerts, Events and Performance counters per minute   

Tags: OpsMgr and MOM, Service Manager
Technorati Tags: ,

Recently we were attempting to look for spikes of alerts, events or performance counters in our environment to indicate potential floods of information. To track these down, we used queries off the the OperationsManagerDW (to avoid reading from the OperationsManager database). The following dashboard items were integrated into the Service Manager dashboard (discussed previously here). I recently added these SQL queries to those already available here on SystemCenterCentral. The goal of these dashboards is to identify significant changes in how much information is gathered and where is being gathered.

Performance counters gathered per minute for the last hour:

clip_image002

 

SELECT top 60 '', COUNT(*) as "PerfData Inserted Per Minute"

  FROM [OperationsManagerDW].[Perf].[vPerfRaw]

  where DATEDIFF(HOUR, DateTime, GETUTCDATE()) <= 1

  GROUP BY substring(convert(varchar, DateTime, 120),6, 11)

  ORDER by substring(convert(varchar, DateTime, 120),6, 11) DESC

Top performance counters gathered per minute for the last hour:

clip_image004

 

SELECT  Top 10 CounterName, COUNT(*) as "Number of Perf Counters"

  FROM  [OperationsManagerDW].[Perf].[vPerfRaw] as RAW INNER JOIN [OperationsManagerDW].[dbo].[PerformanceRule] as PerfRule

ON RAW.PerformanceRuleInstanceRowId=PerfRule.RuleRowId

  where DATEDIFF(HOUR, RAW.DateTime, GETUTCDATE()) <= 1

  GROUP BY PerfRule.CounterName

  Order by "Number of Perf Counters" DESC

Events gathered per minute for the last hour: (graph below taken from a small environment where events were only gathered over some of the 60 minutes sampled)

clip_image006

 

SELECT top 60 '', COUNT(*) as "Events Inserted Per Minute"

  FROM  [OperationsManagerDW].[Event].[vEvent]

    where DATEDIFF(HOUR, DateTime, GETUTCDATE()) <= 1

  GROUP BY substring(convert(varchar, DateTime, 120),6, 11)

  ORDER by substring(convert(varchar, DateTime, 120),6, 11) DESC

Top events gathered per minute for the last hour:

clip_image008

 

SELECT Top 10 EventDisplayNumber, COUNT(*) as "Number of Events" 

  FROM  [OperationsManagerDW].[Event].[vEvent]

    where DATEDIFF(HOUR, DateTime, GETUTCDATE()) <= 1

  GROUP BY EventDisplayNumber

  Order by "Number of Events" DESC

Alerts gathered per minute for the last hour: (graph below taken from a small environment where alerts were only created over some of the 60 minutes sampled)

clip_image010

 

SELECT top 60 '', COUNT(*) as "Alerts Inserted Per Minute"

  FROM  [OperationsManagerDW].[Alert].[vAlert]

  where DATEDIFF(HOUR, RaisedDateTime, GETUTCDATE()) <= 1

  GROUP BY substring(convert(varchar, RaisedDateTime, 120),6, 11)

  ORDER by substring(convert(varchar, RaisedDateTime, 120),6, 11) DESC

Top alerts generated per minute for the last hour:

clip_image012

 

SELECT Top(10) AlertName, SUM(1) AS AlertCount, SUM(RepeatCount+1) AS "Alert Count With Repeat Count"

FROM [OperationsManagerDW].[Alert].[vAlert] WITH (NOLOCK)

WHERE DATEDIFF(HOUR, RaisedDateTime, GETUTCDATE()) <= 1

GROUP BY AlertName

ORDER BY AlertCount DESC

Summary: These individual dashboard pieces can be combined to provide an OpsMgr dashboard which focuses specifically on what data is being gathered recently in the environment. This would assist not only in identifying significant changes in what is being gathered but also in identifying information which may be able to be tuned for the environment.

 
Posted by  Cameron Fuller  on  11/15/2011
0  Comments  |  Trackback Url  | 0  Links to this post | Bookmark this post with:        
 

Links to this post


Pingback from  TechNet Blogs  on  2/26/2013  7:37 AM







Pingback from  Cameron Fuller - Details to resolve an upgraded...  on  12/19/2013  9:05 AM


Pingback from  MSDN Blogs  on  6/2/2014  3:19 PM



Comments

Name *:
URL:
Email:
Comment:


CAPTCHA Image Validation