[OpsMgr 2012] How to find a specific rule/monitor/discovery in the console - SQL queries / Powershell command ( #SQL #Powershell #OpsMgr #OpsMgr2012 )

Sometimes in the Operations Manager Event log, we have some event/alert  for script error or WMI query error and all the time, the description give your the exact name of the rule/monitor/discovery that cause the issue. In SCOM console, you cannot find the rule/monitor/discovery by their exact name, only by the display name.

For example, here is an event 5500 we have on one server due to an invalid configuration for a monitor: Microsoft.SystemCenter.Agent.HealthService.PrivateBytesThreshold

Log Name:      Operations Manager

Source:        HealthService

Date:          11/28/2013 3:10:43 PM

Event ID:      5500

Task Category: Health Service

Level:         Information

Keywords:      Classic

User:          N/A

Computer:      MyServer.MyDom.Dom

Description:

Frequent state change requests caused the incoming state change request to be dropped due to it being older than the currently recorded state change for this monitor. This could also be due to an invalid configuration for this monitor. 

Affected monitor: Microsoft.SystemCenter.Agent.HealthService.PrivateBytesThreshold

Instance: MyServer.MyDom.Dom

Instance ID: 293C0099-290F-C53C-340E-A5E710CA5B9F

Management Group: MyMgtGroup

Request generated time: 2013-11-28T15:10:43.1658766+01:00

Requested state: Success

Recorded time: 2013-11-28T15:20:35.7827611+01:00

Recorded state Success

To start investigation, you need to retrieve the display name of the rule/monitor/discovery. You can also execute SQL queries on views in the OpsMgrDB or PowerShell commands connected to your management group :

Rules:

SQL:

select DisplayName from ruleview where name = 'Rule.Name'

Command Shell:

(Get-SCOMRule | Where {$_.name -match 'Rule.Name'}).DisplayName

Monitors:

SQL:

select DisplayName from monitorview where Name = 'Monitor.Name'

Command Shell:

(Get-SCOMMonitor| Where {$_.name -match 'Monitor.Name'}).DisplayName

Discoveries:

SQL:

select DisplayName from DiscoveryView where name = 'Discovery.Name'

Command Shell:

(Get-SCOMDiscovery| Where {$_.name -match 'Discovery.Name'}).DisplayName

In our case, powershell command or SQL query give a display name (replace Monitor.Name by Microsoft.SystemCenter.Agent.HealthService.PrivateBytesThreshold in the line) : Health Service Private Bytes Threshold for monitor Microsoft.SystemCenter.Agent.HealthService.PrivateBytesThreshold

We can now retrieve the monitor in the SCOM console and try to investigate the issue  ! :)

This posting is provided "AS IS" with no warranties.

[SQL & SCOM] What Unsealed MP is storing overrides - SQL query

Here is a usefull query that will help you to retrieve where are stored your overrides by unsealed MP.

It can help you to retrieve where are stored the overrides on a specific MP or what overrides are stored in the specific MP (for example the default MP ;) ) to be able to clean up this management pack.


Here is the query to execute on the OperationsManager :

SELECT MP.MPName, MP.MPVersion, OVRMP.MPName AS 'Unsealed MP', OVRMP.MPVersion AS 'Unsealed MP Version', COUNT(AOV.Id) AS 'Number of Overrides'
FROM AllOverrideView AS AOV LEFT OUTER JOIN
                     
Rules AS R WITH (nolock) ON AOV.TargetId = R.RuleId LEFT OUTER JOIN
Monitor AS M WITH (nolock) ON AOV.TargetId = M.MonitorId LEFT OUTER JOIN
DManagementPack AS MP WITH (nolock) ON
    (CASE
    WHEN AOV.OverrideType = 'RuleProperty' THEN R.ManagementPackId
    WHEN AOV.OverrideType = 'RuleConfiguration' THEN R.ManagementPackId
    WHEN AOV.OverrideType = 'MonitorProperty' THEN M.ManagementPackId
    WHEN AOV.OverrideType = 'MonitorConfiguration' THEN M.ManagementPackId
    WHEN AOV.OverrideType = 'DiscoveryProperty' THEN D .ManagementPackId
    WHEN AOV.OverrideType = 'DiscoveryConfiguration' THEN D .ManagementPackId
    END) = MP.ManagementPackId INNER JOIN
ManagementPack AS OVRMP WITH (nolock) ON OVRMP.ManagementPackId = AOV.ManagementPackId
WHERE     (MP.MPName IS NOT NULL)
GROUP BY MP.MPName, OVRMP.MPName, MP.MPVersion, OVRMP.MPVersion
ORDER BY MP.MPName, 'Unsealed MP'

Result exported to excel should be like :

    Click here to download it !

This posting is provided "AS IS" with no warranties.

[SQL & SCOM] List SCOM groups for a list of servers

SELECT TargetMonitoringObjectDisplayName, SourceMonitoringObjectDisplayName AS 'Group'
FROM RelationshipGenericView
WHERE TargetMonitoringObjectDisplayName in ('MyServerFullName',
'MyServerFullName1')
AND (SourceMonitoringObjectDisplayName IN
(SELECT ManagedEntityGenericView.DisplayName
FROM ManagedEntityGenericView INNER JOIN
(SELECT BaseManagedEntityId
FROM BaseManagedEntity WITH (NOLOCK)
WHERE (BaseManagedEntityId = TopLevelHostEntityId) AND (BaseManagedEntityId NOT IN
(SELECT R.TargetEntityId
FROM Relationship AS R WITH (NOLOCK) INNER JOIN
dbo.fn_ContainmentRelationshipTypes() AS CRT ON R.RelationshipTypeId = CRT.RelationshipTypeId
WHERE (R.IsDeleted = 0)))) AS GetTopLevelEntities ON
GetTopLevelEntities.BaseManagedEntityId = ManagedEntityGenericView.Id INNER JOIN
(SELECT DISTINCT BaseManagedEntityId
FROM TypedManagedEntity WITH (NOLOCK)
WHERE (ManagedTypeId IN
(SELECT DerivedManagedTypeId
FROM dbo.fn_DerivedManagedTypes(dbo.fn_ManagedTypeId_Group()) AS fn_DerivedManagedTypes_1))) AS GetOnlyGroups ON
GetOnlyGroups.BaseManagedEntityId = ManagedEntityGenericView.Id))
ORDER BY 'Group'

Result will be like :

TargetMonitoringObjectDisplayName	Group
MyServerFullName1	agent group
MyServerFullName	agent group
MyServerFullName1	Agent Managed Computer Group
MyServerFullName	Agent Managed Computer Group
MyServerFullName1	All Windows Computers
MyServerFullName	All Windows Computers
MyServerFullName1	IIS 2003 Computer Group
MyServerFullName	IIS 2003 Computer Group
MyServerFullName1	IIS Computer Group
MyServerFullName	IIS Computer Group
MyServerFullName1	Windows Server 2003 Computer Group
MyServerFullName	Windows Server 2003 Computer Group
MyServerFullName1	Windows Server Computer Group
MyServerFullName	Windows Server Computer Group
MyServerFullName1	Windows Server Instances Group
MyServerFullName	Windows Server Instances Group

This posting is provided "AS IS" with no warranties.

System Center Operations Manager 2007 usefull SQL Queries

I will try to often update this post with the SQLqueries I've found or developped for specific report needs.

Last update : 2012/03/07

• Retrieve discovered objects for a specific class
• List SCOM groups for a list of servers
• Get SCOM Groups members using a SQL Query on OperationsManager DB
• List all Manual Reset Monitors

This posting is provided "AS IS" with no warranties.