Archive | January, 2013

Touchdown : ScomExcelWorkbook V2 is released

28 Jan

Hi Community,

After some delays I have finished the V2 version of the ScomExcelWorkbook.
See old  V1 post here:

New features

1) More types to query

The types below can now be used in the Type column:

2) Combine data on same sheet

When you make a pivot table from a SCOM data sheet you will notice that if you want to combine data of 2 data sheets into one pivot table it will be a challenge to get it work. So I have made a feature that you can append data to one SCOM data sheet. The only thing you will have to do is to use the same sheet name and type in the query rows. See below for a example.


3) Extended Properties

Since I was too lazy to hard type every property name in a column I made it dynamic. Cost me some time to get the object type casting generic but again I learned a lot more of C# reflection.  And that’s what I do it for “learning on the job”.

4) Optimization

As always , some speed-up and code simplifying was done.


Okay nice but how to get it…

In my V1 version I decided to only give the download to people that did a PM. I have had a lot of PMs and good responses. This was great but took a lot of time to process. So now I will publish it on my public SkyDrive link below. But you are free to leave me a comment I really would appreciate that.!1363&authkey=!AOoo44wQmagQyOI

Download it and give it a try. Please please let me know if you like it or have suggestions/problems.

O yea before I forget:

The sheet contains sample query’s you can delete every line after row 4.  And put your own ones in. Some help can be found here:


Happy Scomming,

Michel Kamp

No Mr. SCOM I told you not a availability state report but a performance state report I want!

16 Jan

Sometimes you wonder why not all the reports are as the should be. For example of course you are known with the availability report . Just pick a target and period and you will get a nice report telling you when a target when unhealthy.


The challenge.

Okay nice …. but I want a report not based on the availability data but on the performance or configuration or security data. But wait this is build into the availability report isn’t it ?

looking at the report description:


“For every managed object within System Center Operations Manager, monitors configured in each of the disciplines below determine an objects time in state and then roll-up to an objects overall health. The availability report by default shows an objects time in state as per the monitors that roll-up within the availability discipline.

Entity health

Availability   <= this you get

Configuration <= this you want

Performance <= ..

Security <= ..

O no , it looks like not. So yes it’s a real challenge. That the way we like it.


Since the availability report was intended to be used for this but at the end it looks like the SCOM program team decided to make it locked on ‘availability’ only.  I know this because when you look into the report definition you will see:


So the report is using only the availability rollup as state calculation data. AND this parameter is hidden for gurus as us. How dear they Knipogende emoticon

So we can solve it on several ways. The root solution is that we want to change the value ‘System.Health.AvailabilityState’ to ‘System.Health.PerformanceState’ or ‘System.Health.ConfigurationState’  or ‘System.Health.SecurityState’ to get the report state type we want.

1) export the report from report service and edit the hidden value to false. Import the report and open it in the SCOM console and edit the MonitorName value to for example System.Health.PerformanceState . Run the report and you are done.

2) make a normal report run using the non modified availability report and save it to a Management pack. Now export the MP and open it in notepad and edit the MP.

3) make a normal report run using the non modified availability report save it as favorite. Now open SQL enterprise and lookup the report in the table dbo.favoritereport . Change the ReportParameterValues with the changed parameters.

I know you are thinking right now… what would you do Michel…

I would go for option 1. Because I would also change the report definition to have the correct name as ‘Performance availability’ ect.. and save it also under a different name. Because you must be aware that if you only change the report value to hidden = false and don’t change the report file name….. The next time you import a new service pack or MP version it could be that your report is going to be overwritten… So said that go for the more save one and choose 2.

Let’s go!

1) So make the normal availability report in the SCOM console

2) Save it to a MP


3) Export the MP

4) Edit the MP with notepad


5) import it in scom. (leave the mp version number unchanged)

6) wait a few minutes and you will see the report in the console

Below the end result. Also notice that you can still click to sub report that that this report are also of the state type you wanted!.


Yes I know that you will have to do this for every 3 report types because you can’t change the monitor type runtime. At the end the decision is at you to use step 1 , 2 or 3.

The End

Every time I tell my self make a short blog post! But every time I notice that I am failing.. But who cares…  (yes okay.. my wife) Knipogende emoticon 

Happy scomming!

Michel Kamp

Authoring SCOM Reports in VS 2010

14 Jan


Short post on how to get you dev environment ready for authoring scom reports.


You have installed SCOM 2012 on SQL 2008. You want to author a custom report using Visual studio 2010. When you open visual studio you will notice that NO BI project template is shown. Normally you selected this project template and selected the new report project to make your custom report. How now to continue ?


Grab a SQL 2012 ISO (YES 2012) and startup the setup.

1) Select installation:


2) New sql or add features


3) Select SQL features Install


4) Now the important step. Select the 3 options here. Most important is the “SQL Server Data Tools”. This features contains the VS BI project template.


5) Step though the install windows.

And now open Visual studio 2010 and create a new project. And what do we see ?

Yes the BI template Knipogende emoticon 



Now you can create the new SCOM reports. Notice also the NEW chart types !!!



Remember that if you use custom report code components you must copy the correct .dll assemble to the directory:

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies

The End.

Happy Scomming

Michel Kamp