SCORCH: Find runbook in folder structure

When you know a runbook exists but you can't find the folder it resides in, you can try this SQL query. To retrieve the complete path from the root of the folder structure down to the runbook, i used a Common Table Expression (CTE). It works like a recursive function.

with ItemHierarchy (UniqueID, Name, ParentID, Path, Level) as
(
 select fol.UniqueID, fol.Name, fol.ParentID, CAST(fol.Name as varchar(max)), 0
 from FOLDERS fol
 where fol.ParentID IS NULL

 union all

 select fol.UniqueID, fol.Name, fol.ParentID, CAST(par.Path + '\' + fol.Name AS varchar(max)), par.Level + 1
 from FOLDERS fol
 inner join ItemHierarchy par on fol.ParentID = par.UniqueID
)
select pol.Name as 'PolicyName', ith.Name as 'FolderName', ith.Path as 'Folder Path'
from POLICIES pol
inner join ItemHierarchy ith on pol.ParentID = ith.UniqueID
where pol.Name like '%RunbookName%'

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.

SCOM: Upgrading to SCOM 2007 R2

Last week i was busy upgrading multiple SCOM 2007 SP1 environments to R2.

The upgrade process to R2 is actually very straigt forward. That is, if your environment is also straight forward.

A collegue of my was presented with an upgrade error when he tried to upgrade the Reporting Server and the Data Warehouse Database in the background.

Eventually it was caused because the user account the SCOM Reporting Server upgrade was started with, was configured as a user on the database without SQL roles.

This takes some time to figure out, especially when you have to work with different teams. A team for SCOM, a team for SQL and for example a team for IIS.

That's why here is a R2 upgrade info list for your convenience:

• What's new in SCOM R2
• R2 Webcasts
• SCOM R2 Product Documentation

But most important:

• SCOM 2007 Upgrade Guide (Notice the Improving Upgrade Performance article)
• SCOM 2007 Deployment Scenarios

And remember, always create a backup of your database before installing a upgrade.