Using SQL queries for maintenance
One of the most visited blog posts related to OpsMgr on the internet has to be Kevin Holman's 'Useful Operations Manager SQL Queries' post - http://tinyurl.com/kholmansqlqueries. Originally written for OpsMgr 2007 but still fully relevant for later releases, this blog post contains a goldmine of SQL queries that you can use to carry out maintenance and troubleshooting tasks in your environment.
Over the years, I've probably used every one of those SQL queries but these are the ones I tend to re-use on a regular basis:
- Large table query
- Top 20 alerts in an Operational database, by repeat count
- Top 20 performance insertions by perf object and counter name
- State changes per day
- Noisiest monitors changing state in the database in the last 7 days
- Find the rules collecting the most performance signature data in the database
- Find all groups for a given computer/object
- Set an individual agent back to Remotely Manageable
- Find a computer name from a Health...
