Integrating SCOM to CMDB Portal

The Cireson SCSM CMDB portal is awesome because you can browse any Config Item class that you have resting inside of Service Manager. But as the Operations Manager folks amongst us already know, syncing discovered SCOM Inventory into SCSM offers a wealth of integration possibilities around Incident management, automation, and more.

This latest customization integrates these two worlds in a meaningful way - SCOM performance data rendered on the fly per SCSM Config Item (Computer) page.

In the above example, navigating into a discovered Windows Computer, a SCOM DW query is run to retrieve the last several days of (in percent) Free Disk on C, Used Memory, and CPU utilization. As you can also see the chart renders above the navigation tabs of the CI but underneath the CI name. So let's built it in 4 steps.

1. Creating a Cireson Data Source to the SCOM DW (done through the portal Admin pane)
2. Adding a new SQL query for the SCOM DW data source (done through the portal Admin pane)
3. The viewSCOMPerformance.js is then modified to use the GUID of the query that was created in step 2.
4. Update your custom.js to call viewSCOMPerformance.js

The following SQL query modified from Kevin Holman's original, pulls a few performance counter statistics from the SCOM DW. However a few modifications have been made here - this has gone the SQL CTE route, multiple counters are being thrown together in a single dataset, and finally the use of a {{computername}} token has been introduced. This is a variable we'll pass contextually based on the Computer CI we're looking at in the CMDB portal. Give this query a unique name like "Computer Statistics" or something.

with cte_FreeDisk (PerfDate_Disk, FreeDisk) as (
       select convert(date, DateTime) as 'PerfDate', avg(SampleValue) as 'FreeDisk'
       from   Perf.vPerfRaw pvpr 
                     inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
                     inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId 
                     inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
       WHERE  Path = '{{computername}}'
                     and (CounterName = '% Free Space' and DisplayName = 'C:')
       group by convert(date, DateTime) ),
cte_UsedMemory (PerfDate_Mem, UsedMemory) as (
       select convert(date, DateTime) 'PerfDate'
                     ,avg(SampleValue) as 'UsedMemory' 
       from   Perf.vPerfRaw pvpr 
                     inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
                     inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId 
                     inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
       WHERE  Path = '{{computername}}'
                     and CounterName = 'PercentMemoryUsed'
       group by convert(date, DateTime) ),
cte_ProcessorUtilization (PerfDate_CPU, UtilizedCPU) as (
       select convert(date, DateTime) 'PerfDate'
                     ,avg(SampleValue) as 'ProcessorUtilization' 
       from   Perf.vPerfRaw pvpr 
                     inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
                     inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId 
                     inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
       WHERE  Path = '{{computername}}'
                     and CounterName = '% Processor Time'
       group by convert(date, DateTime) )


select um.PerfDate_Mem
              ,um.UsedMemory
              ,fd.FreeDisk
              ,pu.UtilizedCPU
from   cte_UsedMemory um
              left join cte_FreeDisk fd on um.PerfDate_Mem = fd.PerfDate_Disk
              left join cte_ProcessorUtilization pu on um.PerfDate_Mem = pu.PerfDate_CPU

With that query saved we need to run a query against the ServiceManagement DB. It should go without saying, change the title if you've named your query something different.

select *
from DataSource
where title = 'Computer Statistics'

Alternatively, you can also just add the Id column to the following view and then find the query by name.

Take note of the Id field that is returned from this. Open up the viewSCOMPerformance.js file and replace QueryGuidGoesHERE with the Id returned from the query. This is on Line 9.

You'll also notice the Computer's name is then passed as the computername parameter to the query. That's it! Granted this is only focused on computers, but hopefully this provides enough information for you to see how you could modify this to pull performance counters for other types of discovered SCOM inventory.

My thanks to @Geoff_Ross, @Justin_Workman for their pointers, Kevin Holman's classic SCOM SQL Queries, and our DBA over here for helping bring this together.