Q:

How can you access raw Microsoft SCOM metrics?

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

A:

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.

