Integrating SCOM to CMDB Portal
Adam_Dzyacky
Product Owner Contributor Monkey ✭✭✭✭✭
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.
- Creating a Cireson Data Source to the SCOM DW (done through the portal Admin pane)
- Adding a new SQL query for the SCOM DW data source (done through the portal Admin pane)
- The viewSCOMPerformance.js is then modified to use the GUID of the query that was created in step 2.
- 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.
Comments
Good stuff @Adam_Dzyacky - I'm glad I was along for the ride :)
I've made an update to this that introduces Alert counts alongside the performance columns as well as an Alert Grid that shows the individual alerts for the same time frame.
The original query has been updated and a new one has been added to build the Alert grid on the right. In order to render this, repeat the steps above just adding a new Query ID for the second data source defined in the updated viewSCOMPerformance.js file. Both queries continue to point at the SCOM DW in order to show this data.
I'd like to think I've made this SCOM integration even better, because it now sits inside a navigation node and behaves similar to the other pages.
In order to achieve this new look, we need to copy the contained Microsoft.Windows.Computer.js form into \CustomSpace\ConfigItem. This updated form contains a definition for the new Operations Manager page as seen above/below and like other forms placed into CustomSpace acts as an override for the default out of box one.
With the new section defined, we'll get "Operations Manager" as a navigate node for Computers. With the contained <div>'s set, the JavaScript simply swaps and updates these on load with the charts.
With semi-generic enough pages and dashboards now. You can continue to customize charts for things other than Windows Computers coming from SCOM or use this to serve as a primer to any other number of potential integrations you're looking to perform within the context of a Config Item page.
While I added it to the ACS integration, I forgot to come back here and update the Alert Grid with right click functionality! Because while it's great to have SCOM information directly on the SCSM portal. It's even better when you can right click on this SCOM Alert Grid to kick open a new tab and search your very own Cireson SCSM Knowledge Base based on the Alert Title.
I've left the option of "Console Log Row Details" there which does nothing more than perform a console.log(gridrow) so you can get started with playing with the data yourself for any number of custom right click tasks. But you can easily disable it by commenting the task out.