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.FullName) - LEN(REPLACE(TME.FullName, '_', '')) > 1
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
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.
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!