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

Loading

14 thoughts on “SCOM: Agents not submitting performance data

  1. Pingback: OpsMan » SCOM: Agent health and you

    1. Warren Kahn Post author

      Hi Stevan,
      Usually clearing the agent cache resolves this issue but 1000 rows is quite a large amount, how many agents do you have in your environment, as this could point to a deeper issue?

      Reply
  2. Kap

    Hi Warren,

    This is one of the great script, we are using every week, can we exclude Maintenance mode servers from the script output. I tried but no luck 🙁

    Reply
    1. Warren Kahn Post author

      Hi Kap,

      The script was designed by a college of mine, I would suggest adding the maintenance mode criteria to the first select statement but I know you’ll need an additional join.

      Reply
  3. steven

    DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C]
    and d.IsVirtualNode_E817D034_02E8_294C_3509_01CA25481689 what i need to replace this?

    Reply
  4. Lee

    This script is amazing, thanks for sharing!

    I had an issue where every agent reported issues with collecting CPU counter. I resolved this by changing objectname =’Processor’ to objectname =’Processor Information’

    Reply
  5. Steve

    I try and execute the SQL query against the “OperationsManager” database, this is using OM1801 and SQL Server 2016

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

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘‘’.

    Any ideas most appreciated.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.