.jpg/:/cr=t:0%25,l:0%25,w:100%25,h:100%25/rs=w:1280){kind=logo}
HeartBeat Check Query for an SCOM Agent:
Select mh.INSTALLTIME,mh.INSTALLEDBY,bm.displayname,av.isAvailable from dbo.MT_HealthService mhinner join BaseManagedEntity bm on mh.basemanagedentityid=bm.basemanagedentityidinner join dbo.Availability av on av.basemanagedentityid =mh.basemanagedentityidWHERE bm.DISPLAYNAME IN('ServerName')
Query to Update for all Manually installed Agent:
UPDATE MT_HealthService SET IsManuallyInstalled=0WHERE IsManuallyInstalled=1
Query to Update for specific Manually installed Agent:
UPDATE MT_HealthService SET IsManuallyInstalled=0 WHERE IsManuallyInstalled=1 ANDBaseManagedEntityId IN (select BaseManagedEntityID from BaseManagedEntity whereBaseManagedTypeId = 'AB4C891F-3359-3FB6-0704-075FBFE36710' ANDDisplayName = 'ServerNamewithFQDN')
Query to see Maintenance History on DW:
Query 1:
Use OperationsManagerDW
SELECT ME.DisplayName, MM.StartDateTime,MM.EndDateTime,MMH.ScheduledEndDateTime FROM ManagedEntityas ME WITH (NOLOCK)INNER JOIN MaintenanceMode As MM ON ME.ManagedEntityRowId = MM.ManagedEntityRowIdINNER JOIN MaintenanceModeHistory as MMH ON MM.MaintenanceModeRowId = MMH.MaintenanceModeRowIdwhere ME.DisplayName Like ('%ServerName%')
Query 2:
Use OperationsManagerDW
SELECT ME.DisplayName, MM.StartDateTime,MM.EndDateTime,MMH.ScheduledEndDateTime, MMH.UserID FROM ManagedEntityas ME WITH (NOLOCK)INNER JOIN MaintenanceMode As MM ON ME.ManagedEntityRowId = MM.ManagedEntityRowIdINNER JOIN MaintenanceModeHistory as MMH ON MM.MaintenanceModeRowId = MMH.MaintenanceModeRowIdwhere 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.basemanagedentityidinner join dbo.Availability av with(nolock) on av.basemanagedentityid =mh.basemanagedentityidinner join dbo.MaintenanceMode MM with(nolock) on mm.basemanagedentityid = mh.basemanagedentityidwhere 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 mhinner join BaseManagedEntity bm on mh.basemanagedentityid=bm.basemanagedentityidinner join dbo.Availability av on av.basemanagedentityid =mh.basemanagedentityidinner join dbo.MaintenanceMode MM on mm.basemanagedentityid = mh.basemanagedentityidwhere 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 bminner join Alert A on bm.basemanagedentityid=A.basemanagedentityidWhere A.resolutionState = 0
Query 2:
Use OperationsManager
Select AlertName,AlertDescription,ResolutionState,Severity,Priority,TimeRaised,LastModified,RepeatCount from alertwhere --alertname like '%biztalk%' andResolutionState = 0and 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 AAinner join Alert.vAlertParameter AP on AA.AlertGuid=AP.AlertGuidinner join managedentity ME on AA.ManagedEntityRowid = ME.ManagedEntityRowidwhere 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 bminner join Alert A on bm.basemanagedentityid=A.basemanagedentityidwhere ruleID = 'f1062d9a-a881-2bae-9c11-de38e7a8be4e' --7011and 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 ECinner join event.vEvent EE on EE.LoggingComputerRowId=EC.EventLoggingComputerRowIdinner join Event.vEventDetail ED on ED.EventOriginId=EE.EventOriginIdwhere 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 ECinner join event.vEvent EE on EE.LoggingComputerRowId=EC.EventLoggingComputerRowIdinner join Event.vEventDetail ED on ED.EventOriginId=EE.EventOriginIdwhere EE.EventNumber like '529'and EE.Datetime >= (select DATEADD(HOUR, -10, GETDATE()))
SCOM Alerts:
Use OperationsManagerDW
SELECTvAlert.RaisedDateTime,vEntity.DisplayName,vAlert.AlertName,vAlert.Severity,vAlert.Priority,vAlert.AlertDescriptionFROMalert.vAlert vAlertinner joinvManagedEntity vEntityonvAlert.ManagedEntityRowId = vEntity.ManagedEntityRowIdWHEREvEntity.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 ServerFROM Relationship R WITH (NOLOCK)JOIN BaseManagedEntity SourceBMEON R.SourceEntityID = SourceBME.BaseManagedEntityIDJOIN BaseManagedEntity TargetBMEON R.TargetEntityID = TargetBME.BaseManagedEntityIDWHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication()AND SourceBME.DisplayName not in (select DisplayNamefrom dbo.ManagedEntityGenericView WITH (NOLOCK)where MonitoringClassId in (select ManagedTypeIdfrom 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 ServerFROM Relationship R WITH (NOLOCK)JOIN BaseManagedEntity SourceBMEON R.SourceEntityID = SourceBME.BaseManagedEntityIDJOIN BaseManagedEntity TargetBMEON R.TargetEntityID = TargetBME.BaseManagedEntityIDWHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceSecondaryCommunication()AND SourceBME.DisplayName not in (select DisplayNamefrom dbo.ManagedEntityGenericView WITH (NOLOCK)where MonitoringClassId in (select ManagedTypeIdfrom dbo.ManagedType WITH (NOLOCK)where TypeName = 'Microsoft.SystemCenter.GatewayManagementServer')and IsDeleted ='0')AND SourceBME.DisplayName not in (select DisplayNamefrom dbo.ManagedEntityGenericView WITH (NOLOCK)where MonitoringClassId in (select ManagedTypeIdfrom 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 ServerFROM Relationship R WITH (NOLOCK)JOIN BaseManagedEntity SourceBMEON R.SourceEntityID = SourceBME.BaseManagedEntityIDJOIN BaseManagedEntity TargetBMEON R.TargetEntityID = TargetBME.BaseManagedEntityIDWHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication()AND SourceBME.DisplayName in (select DisplayNamefrom dbo.ManagedEntityGenericView WITH (NOLOCK)where MonitoringClassId in (select ManagedTypeIdfrom 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 ServerFROM Relationship R WITH (NOLOCK)JOIN BaseManagedEntity SourceBMEON R.SourceEntityID = SourceBME.BaseManagedEntityIDJOIN BaseManagedEntity TargetBMEON R.TargetEntityID = TargetBME.BaseManagedEntityIDWHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceSecondaryCommunication()AND SourceBME.DisplayName in (select DisplayNamefrom dbo.ManagedEntityGenericView WITH (NOLOCK)where MonitoringClassId in (select ManagedTypeIdfrom 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