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