Querying Distributed Application data from SCOM Data Warehouse

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


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] 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.FullNameLEN(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


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 I will show you some examples created with Microsofts PowerBI. Don't miss it! Subscribe to our newsletter right now by clicking here!