Kevin Holman, who writes a lot about SCOM2007, posted an article some time ago with a bunch of extremely useful SQL queries to use with SCOM2007. Over several installments, I’ll take that file and show you some useful SCOM PoSh tools you can use to - including a set of cmdlets I wrote to help the process out.

First, let’s take the actual file itself. I’ve taken it and cleaned it up some, changing formats and fixing errors I found, and made it into an actual .SQL file you can use inside of SQL Studio.

Download the file, and save it somewhere useful. SQL Studio 2005 likes to save query files in My Documents\SQL Server Management Studio\Projects, and it’s the first place it goes when you select open, so that makes a great choice.

If you’re not used to SQL server, you might want to know how to use this file. When you open up the file, you’ll notice a ton of different queries - surely you don’t want to run them all at once¹ - and you don’t have to. To run an individual query, simply highlight it and either hit F5, or click on the execute button.

That’s all there is to it. If you wanted to take it a step further, you could hardcode the database name and user before the table name (So instead of “from ManagedEntityGenericView” you’d alter it to (In my case, yours may be different) “from OperationsManager.dbo.ManagedEntityGenericView”. That way, you don’t even have to worry about switching which database to use before running it.

Next up, how to make these more useful to you on an every day basis. And if you’re not one to blindly download things, read on for the pasted SQL file.

Read the rest of this entry »

1. And don’t call me Shirley [↩]