Useful Operations Manager 2007 SQL queries

Kevin Holman’s OpsMgr Blog is great, and provided me with many sql scripts from here

Of course sometimes I can’t find the right script when I want it and I will likely over time add things from other sources so I am duplicating some of his work here, and I will add to is as I personally use the scripts.

List of all management packs and version number

SELECT MPName, MPFriendlyName, MPVersion, MPIsSealed FROM ManagementPack WITH(NOLOCK) ORDER BY MPName 

Find Management Pack name from Workflow name (Change rulename)

SELECT MPName FROM ManagementPack WITH(NOLOCK) where Managementpackid = (SELECT ManagementPackID FROM Rules WHERE Rulename =’MomUIGeneratedRuleb0bac41041dc420abc0926dcdd7a8c23‘)

 Find what alerts are repeating most often

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

Find a rule friendly name from the GUID provided on an alert.  (change out your own rule guid)

SELECT LTValue FROM Rules
INNER JOIN LocalizedText LT ON LT.ElementName = rules.Rulename
WHERE rules.Ruleid=(SELECT ruleid FROM Rules WHERE Rulename =’MomUIGeneratedRuleb0bac41041dc420abc0926dcdd7a8c23‘)

Count the number of  discovered objects by type (from Pavlick.Net)

SELECT mt.ManagedTypeID, mt.TypeName, COUNT(*) AS NumEntitiesByType
FROM BaseManagedEntity bme WITH(NOLOCK)
LEFT JOIN ManagedType mt WITH(NOLOCK) ON mt.ManagedTypeID = bme.BaseManagedTypeID
WHERE bme.IsDeleted = 0
GROUP BY mt.ManagedTypeID, mt.TypeName
ORDER BY COUNT(*) DESC

Leave a Reply

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