HeartBeat Check Query for an SCOM Agent:

 

Select mh.INSTALLTIME,mh.INSTALLEDBY,bm.displayname,av.isAvailable from dbo.MT_HealthService mh

inner join BaseManagedEntity bm on mh.basemanagedentityid=bm.basemanagedentityid

inner join dbo.Availability av on av.basemanagedentityid =mh.basemanagedentityid

WHERE bm.DISPLAYNAME IN('ServerName')

 


 

Query to Update for all Manually installed Agent:

 

UPDATE MT_HealthService SET IsManuallyInstalled=0 

WHERE IsManuallyInstalled=1



Query to Update for specific Manually installed Agent:

 

UPDATE MT_HealthService SET IsManuallyInstalled=0 WHERE IsManuallyInstalled=1 AND 

BaseManagedEntityId IN (select BaseManagedEntityID from BaseManagedEntity where 

BaseManagedTypeId = 'AB4C891F-3359-3FB6-0704-075FBFE36710' AND 

DisplayName = 'ServerNamewithFQDN')

 


 

Query to see Maintenance History on DW:

 

Query 1:

 

Use OperationsManagerDW

SELECT ME.DisplayName, MM.StartDateTime,MM.EndDateTime,MMH.ScheduledEndDateTime FROM ManagedEntity


 as ME WITH (NOLOCK) 

INNER JOIN MaintenanceMode As MM ON ME.ManagedEntityRowId = MM.ManagedEntityRowId 

INNER JOIN MaintenanceModeHistory as MMH ON MM.MaintenanceModeRowId = MMH.MaintenanceModeRowId

where ME.DisplayName Like  ('%ServerName%') 

 

Query 2:

 

Use OperationsManagerDW

SELECT ME.DisplayName, MM.StartDateTime,MM.EndDateTime,MMH.ScheduledEndDateTime, MMH.UserID FROM ManagedEntity


 as ME WITH (NOLOCK)

INNER JOIN MaintenanceMode As MM ON ME.ManagedEntityRowId = MM.ManagedEntityRowId 

INNER JOIN MaintenanceModeHistory as MMH ON MM.MaintenanceModeRowId = MMH.MaintenanceModeRowId

where ME.DisplayName Like  ('%ServerName%')

and MM.StartDateTime >= '2015-02-20'

order by StartDateTime desc

 


 

Check the Genuine Agents status which are unhealthy:

 

Use OperationsManager

Select mh.INSTALLTIME,mm.isinmaintenancemode,mh.INSTALLEDBY,bm.displayname,av.isAvailable from dbo.MT_HealthService mh with(nolock) 


inner join BaseManagedEntity bm with(nolock) on mh.basemanagedentityid=bm.basemanagedentityid

inner join dbo.Availability av with(nolock) on av.basemanagedentityid =mh.basemanagedentityid

inner join dbo.MaintenanceMode MM with(nolock) on mm.basemanagedentityid = mh.basemanagedentityid

where mm.isinmaintenancemode = 0 and av.isAvailable = 0

 


 

Query to see Maintenance History on Operations Manager Database:

 

Select bm.displayname,av.isAvailable,mm.isinmaintenancemode,MM.starttime[MM start Time],

MM.ScheduledEndTime,MM.endtime[MM End Time] from dbo.MT_HealthService mh

inner join BaseManagedEntity bm on mh.basemanagedentityid=bm.basemanagedentityid

inner join dbo.Availability av on av.basemanagedentityid =mh.basemanagedentityid

inner join dbo.MaintenanceMode MM on mm.basemanagedentityid = mh.basemanagedentityid

where bm.displayname like ('%ServerName%')

 


 

Query to see Alert History:

 

Query 1: For active alert with resolution state new

 

Use OperationsManagerDW

Select A.Alertname,A.AlertID,bm.displayname,bm.path,bm.fullname,A.resolutionState,A.Severity,A.priority,


A.category,A.timeRaised,A.TimeResolved,

A.context,A.RepeatCount from BaseManagedEntity bm

inner join Alert A on bm.basemanagedentityid=A.basemanagedentityid

Where A.resolutionState = 0

 

Query 2:

 

Use OperationsManager

Select AlertName,AlertDescription,ResolutionState,Severity,Priority,TimeRaised,LastModified,RepeatCount from alert


where --alertname like '%biztalk%' and 

ResolutionState = 0

and TimeRaised <= '2015-03-25' and TimeRaised >= '2015-03-19'

 

Query 3:

 

Use OperationsManagerDW

Select AA.Alertname,ME.Path,AA.Severity,AA.priority,


AA.RaisedDateTime,AA.AlertDescription from Alert.vAlert AA

inner join Alert.vAlertParameter AP on AA.AlertGuid=AP.AlertGuid

inner join managedentity ME on AA.ManagedEntityRowid = ME.ManagedEntityRowid

where aa.alertname like '%7011%'

and AA.RaisedDateTime between '2015-10-01' and '2015-10-28'

order by AA.RaisedDateTime asc

 

Query 4:

 

Use OperationsManager

Select bm.path,bm.fullname,A.resolutionState,A.Severity,A.priority,


A.timeRaised,A.TimeResolved,A.AlertParams,

A.RepeatCount from BaseManagedEntity bm

inner join Alert A on bm.basemanagedentityid=A.basemanagedentityid

where ruleID = 'f1062d9a-a881-2bae-9c11-de38e7a8be4e' --7011

and A.timeRaised between '2015-10-01' and '2015-10-28'

 


 

Query to get Event ID Data:

 

Query 1:

 

Use OperationsManagerDW

Select ED.RawDescription, ED.RenderedDescription, EE.Datetime, EE.EventChannelRowId, EE.EventLevelId, EE.EventNumber,


 EE.LoggingComputerRowId, EE.EventPublisherRowId, EC.computername from dbo.EventLoggingComputer EC

inner join event.vEvent EE on EE.LoggingComputerRowId=EC.EventLoggingComputerRowId

inner join Event.vEventDetail ED on ED.EventOriginId=EE.EventOriginId

where EE.EventNumber like '10403'

 

Query 2:

 

Use OperationsManagerDW

Select EC.computername [Server Name], EE.EventNumber [Event ID],


ED.RenderedDescription [Description], EE.Datetime [Date]

from dbo.EventLoggingComputer EC

inner join event.vEvent EE on EE.LoggingComputerRowId=EC.EventLoggingComputerRowId

inner join Event.vEventDetail ED on ED.EventOriginId=EE.EventOriginId

where EE.EventNumber like '529'

and EE.Datetime >= (select DATEADD(HOUR, -10, GETDATE()))

 


 

SCOM Alerts:

 

Use OperationsManagerDW

SELECT


vAlert.RaisedDateTime,

vEntity.DisplayName,

vAlert.AlertName,

vAlert.Severity,

vAlert.Priority,

vAlert.AlertDescription

FROM

alert.vAlert vAlert

inner join

vManagedEntity vEntity

on

vAlert.ManagedEntityRowId = vEntity.ManagedEntityRowId

WHERE

vEntity.DisplayName like '%HCWP%'

AND vAlert.RaisedDateTime > DATEADD(DAY, -31, GETUTCDATE())

ORDER by raiseddatetime desc

 


 

Query to Manually Groom the data for Operations Manager Database:

 

exec p_PartitioningAndGrooming 

 


Query to check the Free Space for DB:

 

Select df.name AS 'FileName' 

, physical_name AS 'PhysicalName' 

, size/128 AS 'TotalSizeinMB' 

, size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)/128.0 AS 'AvailableSpaceInMB' 

, CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)/128.0 AS 'ActualSpaceUsedInMB' 

, (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)/128.0)/(size/128)*100. AS '%SpaceUsed' 

FROM sys.database_files df

 


##Get For Agent FailOver info Which Server Is Primary Server

 

SELECT SourceBME.DisplayName as Agent,TargetBME.DisplayName as Server

FROM Relationship R WITH (NOLOCK) 

JOIN BaseManagedEntity SourceBME 

ON R.SourceEntityID = SourceBME.BaseManagedEntityID 

JOIN BaseManagedEntity TargetBME 

ON R.TargetEntityID = TargetBME.BaseManagedEntityID 

WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication() 

AND SourceBME.DisplayName not in (select DisplayName 

from dbo.ManagedEntityGenericView WITH (NOLOCK) 

where MonitoringClassId in (select ManagedTypeId 

from dbo.ManagedType WITH (NOLOCK) 

where TypeName = 'Microsoft.SystemCenter.GatewayManagementServer') 

and IsDeleted ='0') 

AND SourceBME.DisplayName not in (select DisplayName from dbo.ManagedEntityGenericView WITH (NOLOCK) 

where MonitoringClassId in (select ManagedTypeId from dbo.ManagedType WITH (NOLOCK) 

where TypeName = 'Microsoft.SystemCenter.ManagementServer') 

and IsDeleted ='0') 

AND R.IsDeleted = '0'

 


Get For Agent FailOver info Which Server Is Failover Server

 

SELECT SourceBME.DisplayName as Agent,TargetBME.DisplayName as Server

FROM Relationship R WITH (NOLOCK) 

JOIN BaseManagedEntity SourceBME 

ON R.SourceEntityID = SourceBME.BaseManagedEntityID 

JOIN BaseManagedEntity TargetBME 

ON R.TargetEntityID = TargetBME.BaseManagedEntityID 

WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceSecondaryCommunication() 

AND SourceBME.DisplayName not in (select DisplayName 

from dbo.ManagedEntityGenericView WITH (NOLOCK) 

where MonitoringClassId in (select ManagedTypeId 

from dbo.ManagedType WITH (NOLOCK) 

where TypeName = 'Microsoft.SystemCenter.GatewayManagementServer') 

and IsDeleted ='0') 

AND SourceBME.DisplayName not in (select DisplayName 

from dbo.ManagedEntityGenericView WITH (NOLOCK) 

where MonitoringClassId in (select ManagedTypeId 

from dbo.ManagedType WITH (NOLOCK) 

where TypeName = 'Microsoft.SystemCenter.ManagementServer') 

and IsDeleted ='0') 

AND R.IsDeleted = '0'

 


Get For Gateway Failover info Which Server Is Primary Server

 

SELECT SourceBME.DisplayName as Gateway, TargetBME.DisplayName as Server

FROM Relationship R WITH (NOLOCK) 

JOIN BaseManagedEntity SourceBME 

ON R.SourceEntityID = SourceBME.BaseManagedEntityID 

JOIN BaseManagedEntity TargetBME 

ON R.TargetEntityID = TargetBME.BaseManagedEntityID 

WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication() 

AND SourceBME.DisplayName in (select DisplayName 

from dbo.ManagedEntityGenericView WITH (NOLOCK) 

where MonitoringClassId in (select ManagedTypeId 

from dbo.ManagedType WITH (NOLOCK) 

where TypeName = 'Microsoft.SystemCenter.GatewayManagementServer') 

and IsDeleted ='0') 

AND R.IsDeleted = '0'


 

Get For Gateway Failover info Which Server Is Failover Server

 

SELECT SourceBME.DisplayName As Gateway, TargetBME.DisplayName as Server

FROM Relationship R WITH (NOLOCK) 

JOIN BaseManagedEntity SourceBME 

ON R.SourceEntityID = SourceBME.BaseManagedEntityID 

JOIN BaseManagedEntity TargetBME 

ON R.TargetEntityID = TargetBME.BaseManagedEntityID 

WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceSecondaryCommunication() 

AND SourceBME.DisplayName in (select DisplayName 

from dbo.ManagedEntityGenericView WITH (NOLOCK) 

where MonitoringClassId in (select ManagedTypeId 

from dbo.ManagedType WITH (NOLOCK) 

where TypeName = 'Microsoft.SystemCenter.GatewayManagementServer') 

and IsDeleted ='0') 

AND R.IsDeleted = '0'

Query to get Rules or Monitors name for a specific Event Log Name:

Select convert (xml, mpxml), * from ManagementPack where mpxml like '%<LogName>Security</LogName>%'

 

Check for orphaned health services (e.g. agent):

declare @DiscoverySourceId uniqueidentifier;

set @DiscoverySourceId = dbo.fn_DiscoverySourceId_User();

SELECT TME.[TypedManagedEntityid], HS.PrincipalName

FROM MTV_HealthService HS

INNER JOIN dbo.[BaseManagedEntity] BHS with(nolock)

    ON BHS.[BaseManagedEntityId] = HS.[BaseManagedEntityId]

-- get host managed computer instances

INNER JOIN dbo.[TypedManagedEntity] TME with(nolock)

    ON TME.[BaseManagedEntityId] = BHS.[TopLevelHostEntityId]

    AND TME.[IsDeleted] = 0

INNER JOIN dbo.[DerivedManagedTypes] DMT with(nolock)

    ON DMT.[DerivedTypeId] = TME.[ManagedTypeId]

INNER JOIN dbo.[ManagedType] BT with(nolock)

    ON DMT.[BaseTypeId] = BT.[ManagedTypeId]

    AND BT.[TypeName] = N'Microsoft.Windows.Computer'

-- only with missing primary

LEFT OUTER JOIN dbo.Relationship HSC with(nolock)

    ON HSC.[SourceEntityId] = HS.[BaseManagedEntityId]

    AND HSC.[RelationshipTypeId] = dbo.fn_RelationshipTypeId_HealthServiceCommunication()

    AND HSC.[IsDeleted] = 0

INNER JOIN DiscoverySourceToTypedManagedEntity DSTME with(nolock)

    ON DSTME.[TypedManagedEntityId] = TME.[TypedManagedEntityId]

    AND DSTME.[DiscoverySourceId] = @DiscoverySourceId

WHERE HS.[IsAgent] = 1

AND HSC.[RelationshipId] IS NULL;

 

Objects with no TME or Relationship, probable cause of AgentAssignment workitem failing with “System.ArgumentOutOfRangeException” event 29181:

Select * from BaseManagedEntity where BaseManagedEntityId not in (select BaseManagedEntityid from TypedManagedEntity)

AND BaseManagedEntityId NOT IN (select SourceEntityId from Relationship)

AND BaseManagedEntityid NOT IN (select TargetEntityId from Relationship)


Agent Assignment workitem failure event for config service:

Select

    bme.BaseManagedEntityId,

    bme.FullName

from BaseManagedEntity as bme

where

    bme.BaseManagedEntityId not in (

        select TypedManagedEntityId

        from TypedManagedEntity

    ) and bme.BaseManagedEntityId not in (

        select BaseManagedEntityId

        from TypedManagedEntity

    )


Once we got the BME ID from the above query need to run the following query.

declare @timenow datetime = getutcdate()
exec dbo.p_TypedManagedEntityDelete N'DA65825A-10DC-8979-6CD0-99384051F89E', @timenow
exec dbo.p_TypedManagedEntityDelete N'B5189AC1-8B52-0410-E670-CE85DC2AB9BC', @timenow

 

2 thoughts on “SCOM SQL Queries”

  1. Thanks for this post, great information

    Do you have a query if we want to get the server information such memory and CPU which is allocated for one of the servers which monitored by SCOM, in other words to get server resource information

Leave a Reply

Your email address will not be published. Required fields are marked *