The dashboard below is build out of nessecity for an exception dashboard that shows specific SQL objects thats either in a Warning or Critical state. Below is a screen shot
SUBSTRING(bme.Path, 1, PATINDEX(‘%.%’, bme.Path) – 1) AS servername, bme.DisplayName, s.BaseManagedEntityId, s.HealthState, m.MonitorName,
CONVERT(varchar(500), ‘http://<Put in your webconsole servername here>:51908/ResultViews/ViewTypeHealthExplorer.aspx?targetId=’ + CONVERT(varchar(38),
s.BaseManagedEntityId)) AS URL
FROM State AS s WITH (nolock) INNER JOIN
BaseManagedEntity AS bme WITH (nolock) ON s.BaseManagedEntityId = bme.BaseManagedEntityId INNER JOIN
Monitor AS m WITH (nolock) ON s.MonitorId = m.MonitorId
WHERE (m.MonitorName LIKE ‘Microsoft.SQLServer.%.Monitoring.DBFileSpaceMonitor’) AND (s.HealthState >= 2)
for the other object you just need to change the very last line in above query – the m.Monitorname like part.
Greetings from a sunny South Africa 🙂
With SQL 2008 R2 reporting services you get a huge amount of extra features that you can use in your reports. The infrastructure dashboard screenshot below is a dashboard I built using SQL 2008 R2.
The data for the dashboard is coming straight from the SCOM Operations Database.
Some of the queries that is used:
For the Exchange part:
select sum( case alert.severity when 1 then 1 else 0 end ) as warning, sum(case alert.severity when 2 then 1 else 0 end) as Critical,
sum(repeatcount) as RuleAlertTotalNumber
from alert with(nolock) inner join basemanagedentity with(nolock)
on alert.basemanagedentityid = basemanagedentity.basemanagedentityid inner join managedtype as MT1 with(nolock)
on basemanagedentity.basemanagedtypeid = MT1.ManagedTypeId inner join rules with(nolock)
on alert.ruleid = rules.ruleid inner join managedtype as MT2 with(nolock)
on rules.targetmanagedentitytype = MT2.managedtypeid inner join managementpack with(nolock)
on rules.managementpackid = managementpack.managementpackid
where resolutionstate<>255 and mpname like ‘%Exchange%’
You can then go and take the above query and change the last line to the other Management Pack names. One query per vertical graph
Query for the C: drive status rev counter graph
FROM state AS s with(nolock), BaseManagedEntity as bme
WHERE s.basemanagedentityid = bme.basemanagedentityid AND s.monitorid IN (SELECT MonitorId FROM Monitor with(nolock) WHERE MonitorName like ‘%logicaldisk.freespace%’)
and healthstate>=2 and displayname like ‘C:%’
Query for the TOP 15 parts of the dashboard
select top 15 count(*), a.alertstringname
from alertview a with(nolock), rules r, managementpack m
and mpname like ‘%Exchange%’ and a.resolutionstate<>255
and a.timeraised>=’2012/02/01 00:00:00′
and a.timeraised is not null
group by a.alertstringname
Change the “and mpname like ‘%Exchange%’ part to the other management packs for which you want to display Top 15 alerts for.
This dashboard has drill down capabilities, for example if you click on the “C: Drives unhealthy” rev counter you get the screen below
This dashboard showing the disk status detail is basically a sub report that is launched as an action from the dashboard when you click on the rev counter.
From this detail dashboard if you now click on any server or the corresponding status icon the SCOM web console health explorer for that chosen server will be opened showing you even more detail
Screen Shot of the SCOM web console launched in context for the health of the specific C drive on the specific server