Category Archives: Reporting

SCOM: Monitoring Site Availibility

One of my customers still running SCOM 2007 R2 asked if it would be possible to monitor the availability of their 3 sites so that they could track any outages during the month. The below will also work with SCOM 2012.
Each site has a primary and backup connection and the site is only unavailable if both links fail.

First with the network teams assistance I identified and discovered the Routers and Interfaces responsible for the line connections into each of the sites. Then I create a distributed application for each specific site:

Site mon 1

Scope your container to Device

Site mon 2

Search for the site routers discovered earlier and add them to the container.

Site mon 3

Save and open Configure Health Rollup

Site mon 4

Now because the site is only unavailable in the event of both links failing, set the Health Rollup to “Best Health State of any Member” this will ensure that the DA stays green if even 1 Router is still contactable.

Site mon 5

Below is what you will see in your Distributed Application view.

Site mon 6

Here is a sample of an availability report using the DA for a particular site.

Site mon 7

Happy SCOMing.

If you have another way of achieving this result, I’d be interested to hear about it, drop me a mail or leave a comment.

SCOM: Agents not submitting performance data

Sometimes you might have a situation where all of your agents are showing as healthy in the console but when you try and draw a performance report data is missing.

The below SQL query which has been developed by my colleague Gerrie Louw will identify any agent that has not submitted performance data in the past 4 hours. It does so by checking the following performance counters:

Processor > % Processor Time
LogicalDisk > % Free Space > C:
Memory > Available MBytes

Note: You will probably have to change the DisplayName_ and IsVirtualNode for your OperationsManager database.

if object_id(‘tempdb..#temptable’) IS NOT NULL
DROP TABLE #temptable

SELECT     distinct bmetarget.Name into #temptable
FROM        OperationsManager.dbo.BaseManagedEntity AS BMESource WITH (nolock) INNER JOIN
OperationsManager.dbo.Relationship AS R WITH (nolock) ON
R.SourceEntityId = BMESource.BaseManagedEntityId INNER JOIN
OperationsManager.dbo.BaseManagedEntity AS BMETarget WITH (nolock) ON
R.TargetEntityId = BMETarget.BaseManagedEntityId inner join mtv_computer d on bmetarget.name=d.[DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C]
and d.IsVirtualNode_E817D034_02E8_294C_3509_01CA25481689 is null
WHERE     (bmetarget.fullname like ‘Microsoft.Windows.Computer%’)

if object_id(‘tempdb..#healthstate’) IS NOT NULL
DROP TABLE #healthstate

select  megv.path, megv.ismanaged, megv.isavailable, megv.healthstate into #healthstate
from managedentitygenericview as megv with (nolock) inner join managedtypeview as mtv with (nolock)
on megv.monitoringclassid=mtv.id
where mtv.name =’microsoft.systemcenter.agent’

if object_id(‘tempdb..#perfcpudata’) IS NOT NULL
DROP TABLE #perfcpudata

select Path, ‘CPU’ as ‘Cat’ into #perfcpudata
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where (TimeSampled < GETUTCDATE() AND TimeSampled > DATEADD(MINUTE,-240, GETUTCDATE()))
and objectname =’Processor’ and countername=’% Processor Time’

if object_id(‘tempdb..#perfmemdata’) IS NOT NULL
DROP TABLE #perfmemdata

select Path,’Memory’ as ‘Cat’ into #perfmemdata
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where (TimeSampled < GETUTCDATE() AND TimeSampled > DATEADD(MINUTE,-240, GETUTCDATE()))
and objectname =’Memory’ and countername=’Available MBytes’

if object_id(‘tempdb..#perfdiskdata’) IS NOT NULL
DROP TABLE #perfdiskdata

select Path,’Disk’ as ‘Cat’ into #perfdiskdata
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where (TimeSampled < GETUTCDATE() AND TimeSampled > DATEADD(MINUTE,-240, GETUTCDATE()))
and objectname =’LogicalDisk’ and countername=’% Free Space’ and instancename=’C:’

if object_id(‘tempdb..#temptable1′) IS NOT NULL
DROP TABLE #temptable1
create table #temptable1 (
name nvarchar(250),
cat nvarchar(20),
val nvarchar(2)
)
insert into #temptable1
select name, ‘CPU’ as ‘cat’, ’1′ as ‘val’
from #temptable where name not in
(select path from #perfcpudata)

insert into #temptable1
select name, ‘Memory’ as ‘cat’, ’1′ as ‘val’
from #temptable where name not in
(select path from #perfmemdata)

insert into #temptable1
select name, ‘Disk’ as ‘cat’, ’1′ as ‘val’
from #temptable where name not in
(select path from #perfdiskdata)

if object_id(‘tempdb..#output’) IS NOT NULL
DROP TABLE #output
create table #output (
name nvarchar(250),
cpu nvarchar(2),
memory nvarchar(2),
disk nvarchar(2)
)

insert into #output
select distinct tt.name ,’0′,’0′,’0′
from #temptable1 as tt, #healthstate as hs
where tt.name=hs.path collate SQL_Latin1_General_CP1_CI_AS
and hs.isavailable=1
and hs.ismanaged=1
and hs.healthstate is not null

update #output set cpu=1 where #output.name in (select name from #temptable1 where #temptable1.name=#output.name and #temptable1.cat=’CPU’)
update #output set memory=1 where #output.name in (select name from #temptable1 where #temptable1.name=#output.name and #temptable1.cat=’Memory’)
update #output set disk=1 where #output.name in (select name from #temptable1 where #temptable1.name=#output.name and #temptable1.cat=’Disk’)

select * from #output

You can use this query to build a report such as the one sampled below:

No Perf Report edited

SCOM 2012 – Most Common Alerts Report renders with coloured blocks

Something that was brought to my attention by a colleague of mine the other day. When running the Most Common Alerts report in SCOM 2012, if you select more then 15 for your top N alerts then the report renders with multiple blue, red and green blocks. I then tested on my SCOM 2012 environment and discovered the same situation.

Most Common Alerts
We logged a support call to MS on this one and they were able to replicate the issue in their lab environments. They have since provided us with a fixed version of the report which has been amended to render better for higher amounts of alerts.

In order to update the report follow these steps:

1. Browse to the report server manager http://server/reports
2. Open Microsoft.SystemCenter.DataWarehouse.Report.Library
3. On the drop down menu next to the report Microsoft.SystemCenter.DataWarehouse.Report.MostCommonAlerts select download and make a backup of this report
4. Then click on upload File
5. Browse to the updated .rdl file
6. Select “Overwrite items if it exists” and then click OK
7. Refresh the Reporting Pane in the SCOM console and run the report

Microsoft.SystemCenter.DataWarehouse.Report.MostCommonAlerts