A few weeks ago I pointed out how event collection in some management packs is on by default, in the post TIP: How to Eliminate Event Bloat from AD Client Perspective Monitoring in OpsMgr 2007. I see now fellow MVP David Allen as found a similar event collection issue in Why Not to Let Your ACS DB Run Out of Space !!

In the case of the ACS database full event, it's not really an OpsMgr problem (it's a questionable decision by the MP author in my opinion). I wanted to take this opportunity to again point out that by implementing good daily operational activities, you can identify top collection sources of all kinds (event, alert, performance,  state) and take the appropriate corrective actions before things get out of control.

Top Events T-SQL Query (to help identify the source)

Here's they query from the report I use. This query will give you the event IDs that help narrow down the top event IDs and PublisherName (this gives us the source data we need to identify what's generating the noise)

--  Most common events, grouped by event number, event parameters and publishername.
SELECT top 10 Number as EventID, COUNT(*) AS TotalEvts, Publishername as EvtSrc, EventParameters 
FROM EventAllView with (NOLOCK) 
GROUP BY Number, Publishername, EventParameters 
ORDER BY TotalEvts DESC

Other Good Daily Operational Reports (Queries)

Clearly this isn't the only database insertion activities you should be concerned about. And sometimes you're going to encounter high volume sources of data that you may not be able to simply shut off. Sometimes, you may have a computer with a health issue (be it performance or configuration).

To keep your database tuned, you can look at insertions from a number of angles, such as these shown below:

You can start with some global activity reporting that provide a baseline for database activity, such as

• Number of Alerts Per Day
• Event Count by Day
• Agents Currently Down (grey agents)
• Total Database Size (OpsMgr and DW)
• State Changes per Day
• Largest Database Tables (by rowcount)

Then you can drill down into detail reports, that provide more specific data, such as:

• Top Event-Generating Computers
• Top Performance Data Insertions (per object / counter / instance)
• Top Alert Generating Computers
• Noisiest Monitors (per object) – you can catch flapping monitors this way

When you identify and correct issue through the detail reports, you then go back to the the global activity reports to record the daily baseline for your environment and forecast future activity

Future Installments

I'll post a few queries over the next few days on the way to creating some reports for one of my customers. If time allows, I'll also diagram the workflow to illustrate the process for using these reports together for good database hygiene. End goal will be a good reporting pack and accompanying guide.

RSS | TWITTER