1

I am looking for some inspiration to how I can access metrics collected by SCOM? Essentially I want to extract the CPU, Memory and disk utilisation metrics, normalize and POST to an external SaaS solution via an API.

As SCOM already collects these metrics it seems daft installing another agent to collect, though in this case the second agent is not an option :/

I would appreciate any help.

Thanks,

J

Jamie French
  • 11
  • 1

2 Answers2

3

So, as Roman advised, SCOM has two DBs. Operational and Data Warehouse. An MP, which collects performance data should explicitly save collected data to either DB or both. Normally, you would expect the data to be saved to the both DB from a well designed MP. The major difference between the DBs is that Operational DB stores almost "realtime" data (10-20 minutes interval), but DW DB has hourly and daily aggregated data.

From here, I can give you a couple of examples on both DBs.

Operational DB:

This query lists ALL available Object Names, their Instances and corresponding Counter for a specific Windows Computer, however, you can modify the query for other classes:

select pc.* from PerformanceCounterView pc join TypedManagedEntity tme on tme.TypedManagedEntityId = pc.ManagedEntityId join BaseManagedEntity bme on tme.BaseManagedEntityId = bme.BaseManagedEntityId where (bme.TopLevelHostEntityId = (select BaseManagedEntityId from BaseManagedEntity where FullName = 'Microsoft.Windows.Computer:'+@ServerName)) order by ObjectName, CounterName, InstanceName

Then, when you get PerformanceSourceInternalId, you can extract the actual data by running the following query:

declare @TZOffset as int = DATEDIFF(MINUTE,GETUTCDATE(),GETDATE())
SELECT SampleValue, DATEADD(MINUTE,@TZOffset,TimeSampled) as TS
FROM PerformanceDataAllView
where (PerformanceSourceInternalId = @SrcID)
and (TimeSampled > DATEADD(MINUTE,-@TZOffset,@Start))
and (TimeSampled < DATEADD(MINUTE,-@TZOffset,@End))

NB: all time stamps in the both DBs are in UTC, so the last query translates them back to local time.

Data Warehouse DB:

DECLARE @StartDateSubParam as datetime DECLARE @CurrentServerSubParam as int SET @StartDateSubParam = '2016-01-01' SET @CurrentServerSubParam =(select mecn.ManagedEntityRowId from vManagedEntity mecn where (mecn.Path is null) and (mecn.FullName like 'Microsoft.Windows.Computer:yourServer.Domain.com')) select me.[Path] as ServerName, me.ManagedEntityRowId as ManagedEntityID, AverageValue, MaxValue, MinValue, perfdata.StandardDeviation, perfdata.[DateTime] as SampleDay from Perf.vPerfHourly perfdata join vPerformanceRuleInstance pri on perfdata.PerformanceRuleInstanceRowId = pri.PerformanceRuleInstanceRowId join vPerformanceRule pr on pr.RuleRowId = pri.RuleRowId left join vManagedEntity me on me.ManagedEntityRowId = perfdata.ManagedEntityRowId where (pr.ObjectName = 'Processor Information') and (pr.CounterName = '% Processor Time') and (perfdata.[DateTime] > @StartDateSubParam ) and (me.TopLevelHostManagedEntityRowId = @CurrentServerSubParam)

That query select processor performance for a windows computer with Windows 2008 R2+ (change Processor Information -> Processor for earlier windows versions). You may change names to other available counters and also use either vPerfHourly or vPerfDaily for hourly or daily aggregation.

Best regards.

Max
  • 751
  • 6
  • 10
2

As soon as we are talking about inspiration - I'll provide a high-level answer with no technical details. I hope it's fine, Jamie :) So I can see here three options to do that: 1. Get these metrics via MOM API (aka SCOM SDK). Obvious issue - low performance and extra load on the management server 2. Get metrics directly from SCOM DB. We are talking about metrics, so you may decide to work not with Operational DB but with Datawarehouse DW since all metrics are written to both DBs (some MPs are not doing that, but major MPs always send metrics to both DBs). I also vote for DW DB because it has the more transparent schema. 3. Inject your custom WRITE ACTION in MPs that you want to send data to the SaaS. Of course, it means maximum customization and will not work for sealed MPs...so let's leave this option just to disturb your imagination :)

That's what I can bring to the desk right away. I hope it helps.

Roman.

Roman Yuferev
  • 71
  • 2