SCOM: Average Events Per Day keeps the doctor away

First of all, because I'm very busy with actually working on SCOM projects I can't spend the time blogging about SCOM as much as I would like.

But today I found some time to blog about a simple, but handy, SQL query I used to determine the average number of events stored in the Data Warehouse database per day.

At the base I used a query from Jonathan Almquist. Then I used that query as derative to count and calculate the average number of events per day. You can adjust the number of days, if you want.

select Count(Date) as 'Number Of Days', Avg(Events) As 'Average Number of Events'
From (
SELECT CONVERT(VARCHAR(10), DateTime, 101) AS Date, Count(*) AS Events
FROM Event.vEvent
WHERE (DateTime BETWEEN DATEADD(day, - 6, GETDATE()) AND GETDATE())
GROUP BY CONVERT(VARCHAR(10), DateTime, 101)
) x

So, what can you do with this?
Well, how do you know if your Management Servers can cache the event data collected by your agents when your Data Warehouse is down for a couple of hours.
Running these type of queries can help you understand how much data is stored in the Data Warehouse over time.

Tools like dwdatarp are also very helpfull to understand the data storage of the DWH.

Configuring OperationsManagerDW grooming

Last week i did some maintenance on a Operations Manager 2007 R2 Data Warehouse. Because this piece of OpsMgr maintenance is sometimes 'forgotten', i thought it would be nice to blog about this.

After this initial deployment, there has not been much maintenance. Thanks to the integrated maintenance jobs through the Operations Manager 2007 internal management pack library and having enough disk space, no actual problems did arise.

You should, probably, know that an out-of-the-box deployment of OpsMgr 2007 keeps the collected information in your Operations Manager Datawarehouse for 400 days.

Using the sizing tools from Microsoft and from books like 'Unleashed' you can calculate how much disk space you would need keeping 400 days of historical information.
Also keep in mind the backup method you will need to use, because of the rather large database files. Always consult the Database Administrator for your implementation and maintenance plans.

When a company starts of with OpsMgr 2007 with a number of 500 agents and no known future company aquisitions no problems arise. But when that company begins to expand and more agents are becoming managed by this management group, you should really do some recalculation of the your database growth for your OperationsManagerDW as well as for your OperationsManager DB. There are some pretty handy built-in reports in Operations Manager you can use to see the daily growth. Also use the Operations Console Monitoring views to view the Database Size performance counters.

If you find that the current span of historical data is not right and has to be modified there a multiple ways for doing this.

The old way: Using a sql queries and stored procedure http://aquilaweb.com/blog/index.php?itemid=41
The new and easier way: Using a simple tool, dwdatarp.exe http://blogs.technet.com/momteam/archive/2008/05/14/data-warehouse-data-retention-policy-dwdatarp-exe.aspx

I prefer the new and easier way for obvious reasons :). I you don't want to wait for the next groom workflow, run the procedure 'p_partitioningandgrooming' on the SQL database.

Within the next two weeks i'll be performing a OperationsManagerDW relocation. Of course I'll post the details and my experiences with this actions. Note that a complete 'OpsMgr shutdown' is neccesary to do this, so your agents should be configured with enough cache memory to hold on.