Useful SQL Queries in SCOM DB
Following are the useful SQL queries in SCOM Database.
Gives a list of all Groups:
select distinct DisplayName from vManagedEntity
inner join vRelationship on vManagedEntity.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId
inner join vRelationshipType on vRelationship.RelationshipTypeRowId=vRelationshipType.RelationshipTypeRowId
inner join vRelationshipManagementGroup on vRelationshipManagementGroup.RelationshipRowId=vRelationship.RelationshipRowId
where (vRelationshipType.RelationshipTypeSystemName='Microsoft.SystemCenter.ComputerGroupContainsComputer'
or vRelationshipType.RelationshipTypeSystemName like '%InstanceGroup%')
and vRelationshipManagementGroup.ToDateTime is null
order by DisplayName asc
Gives a list of all Management Packs:
select distinct mp.ManagementPackSystemName,ManagementPackDefaultName from vManagementPack mp
inner join vRule r on r.ManagementPackRowId=mp.ManagementPackRowId
inner join vPerformanceRule pr on pr.RuleRowId=r.RuleRowId
UNION ALL
select '<ALL>','<ALL>'
Order by mp.ManagementPackDefaultName asc
Gives a list of all Rules:
select r.RuleDefaultName,r.RuleSystemName from vRule r
inner join vPerformanceRule pr on pr.RuleRowId=r.RuleRowId
order by r.RuleDefaultName asc
Gives top 20 alerts in an Operational Database, by Alert Count:
SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name
FROM Alertview WITH (NOLOCK)
WHERE TimeRaised is not NULL
GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name
ORDER BY AlertCount DESC
Gives top 20 alerts in an Operational Database, by Repeat Count:
SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name
FROM Alertview WITH (NOLOCK)
WHERE Timeraised is not NULL
GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name
ORDER BY RepeatCount DESC
Gives a list of most common events by event number:
SELECT top 50 EventDisplayNumber, COUNT(*) AS TotalEvents
FROM Event.vEvent
GROUP BY EventDisplayNumber
ORDER BY TotalEvents DESC