Blogg

Querying Distributed Application data from SCOM Data Warehouse

SCOM_DA_Article

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:

'SC_e80bf180b85247248b67679cd7519437_Service_cd2240d6139e4d9a81188c04c8612855'.

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

'Service_49cb64e8085d4d5eb00e460880a0d8d9_Application'.

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. 

<span style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US;"><span style="color: green;">  -- Management pack not sealed </span></span><span style="font-family: Consolas; font-size: 9.5pt;">  </span><span style="color: gray;">AND </span><span style="color: teal;">MP</span><span style="color: gray;">.</span><span style="color: teal;">SealedInd</span><span style="color: gray;">=</span><span style="font-family: Consolas; font-size: 9.5pt;"> 0</span>

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

<span style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US;"><span style="color: green;">  -- Entity not deleted in Console </span></span><span style="font-family: Consolas; font-size: 9.5pt;">  </span><span style="color: gray;">AND </span><span style="color: teal;">MEMG</span><span style="color: gray;">.</span><span style="color: teal;">ToDateTime </span><span style="color: gray;">IS </span><span style="color: gray;">NULL</span><span style="color: gray;"> </span>

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.

SELECT

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
AND
-- Only join to target entities that have more than 1 underscore in FullName
LEN(TME.FullName) - LEN(REPLACE(TME.FullName,'_','')) > 1

WHERE

-- 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
AND MEMG.ToDateTime IS NULL

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

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

SCOM_DA_Article2

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!

Ämnen: System Center Operations Manager