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'
SELECT CONVERT(VARCHAR(10), DateTime, 101) AS Date, Count(*) AS Events
WHERE (DateTime BETWEEN DATEADD(day, - 6, GETDATE()) AND GETDATE())
GROUP BY CONVERT(VARCHAR(10), DateTime, 101)
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.