System Center Operations Manager

Querying Distributed Application data from SCOM Data Warehouse

By October 25, 2015 No Comments


Operations Manager is good at monitoring performance of separate software components.It also has an interface to bundle them together into groups in order to be able to understand what the health state of the whole as a group is.

In SCOM context this is called a Distributed Application. At Approved we treat this (with addition of Live Maps Unity from Savision) as an interface for managing IT Services.

As in most cases after getting data into SCOM and then into SCOM Data Warehouse, some day we want to extract and analyze this data or, better yet, use it as a base to predict future outcomes and deal with issues before users even notice them.

And, as in most cases, extracting and querying SCOM DW Distributed Application data is not really straightforward. Lets start with finding the ‘services’ themselves.

Services (Distributed Applications)

Identifying services is hard partially due to the fact that all of the DAs and their child entities (no matter how many levels underneath it might be) are dumped into one flat table. Yes, they are right there amongst thousands of other managed entities without a proper and predictable identification mechanism. Hence most examples of DA qyerues you will find out there always relate to just one service, name of which you should know.

We want to find all services so that we don’t have to remember how each and every one of them is spelled.

One way we found to work reasonably well is to count underscore characters (‘_’) in the FullName field of ManagedEntity table. So the base for our query is managed entities that have a FullName starting with ‘Service_’ and that have only one underscore in the FullName. In SQL it looks like this:

  -- Identify Services   LEFT(ME.FullName, 8) = 'Service_'
  -- Take only top level components (those that have only 1 underscore in name
   AND LEN(ME.FullName) - 1 = LEN(REPLACE(ME.FullName'_'''))

DA Child Entities

When looking for child entities we deal with some extra variations. If you build your Distributed Application via SCOM Console, then your child entities will have FullName that looks simmilar to this:


When DAs are mapped with LiveMaps, then all child entities have a different pattern for FullName field:


One thing in common though are (again) underscores. Hence we relate only to target entities that have more than one underscore in FullName:

-- Only join to target entities that have more than 1 underscore in FullName  
LEN(TME.FullNameLEN(REPLACE(TME.FullName'_''')) > 1

Additional Checks

In the final example there are two more checks that we make.

First is to make sure that we show only services from unsealed management packs. These will be the actual DAs that you created and not just stuff that landed there because of some MP. 

  -- Management pack not sealed   AND MP.SealedInd= 0

Second one is used for filtering out all that has been deleted already:

  -- Entity not deleted in Console   AND MEMG.ToDateTime IS NULL 

Final query

If we put all of the above together we get this query. Just paste it into Management Studio and it will return all active services that you have in your SCOM data warehouse.


 ME.ManagedEntityGuid AS ServiceBaseManagedEntityGuid,
 ME.DisplayName AS ServiceName,
 TME.ManagedEntityGuid AS ServiceComponentManagedEntityGuid,
 TME.DisplayName AS ServiceComponentName

FROM OperationsManagerDW.[dbo].[vManagedEntity] ME

 INNER JOIN OperationsManagerDW.dbo.vManagedEntityManagementGroup MEMG ON
 ME.ManagedEntityRowId = MEMG.ManagedEntityRowId

 INNER JOIN OperationsManagerDW.[dbo].[vManagedEntityType] MET ON
 ME.ManagedEntityTypeRowId = MET.ManagedEntityTypeRowId

 INNER JOIN OperationsManagerDW.[dbo].[vManagementPack] MP ON
 MET.ManagementPackRowId = MP.ManagementPackRowId

 LEFT OUTER JOIN OperationsManagerDW.[dbo].[vRelationship] R ON
 ME.ManagedEntityRowId = R.SourceManagedEntityRowId

 LEFT OUTER JOIN OperationsManagerDW.[dbo].[vManagedEntity] TME ON
 R.TargetManagedEntityRowId = TME.ManagedEntityRowId
 -- Only join to target entities that have more than 1 underscore in FullName
 LEN(TME.FullName) - LEN(REPLACE(TME.FullName,'_','')) > 1


 -- Identify Services
 LEFT(ME.FullName, 8) = 'Service_'

 -- Take only top level components (those that have only 1 underscore in name
 AND LEN(ME.FullName) - 1 = LEN(REPLACE(ME.FullName,'_',''))

 -- Entity not deleted in Console

 -- Management pack not sealed
 AND MP.SealedInd= 0

And the result should display a nice window with Distributed Applications from the Operations Manager Datawarehouse.


What’s next?

Now that we’ve found all of our services we have some more freedom to create user friendly availability and capacity reports. In the next blog post we will show you how to forecast capacity data from Operations Manager. Don’t miss it!