Search

This time I will do a post on retrieving SCOM data into Excel. In a previous blog post series I talked about how to process SCOM data into pivot tables. See: https://michelkamp.wordpress.com/category/powerpivot/ But you had to use direct SQL query’s to get this data. So this is what I wanted to eliminate. Also I wanted to get SCOM data from more than one management group using different credentials and only over an SDK connection.

So… can I make you lucky today ?

I programmed a prototype in a C# EXCEL sheet that will do all the work. You will have 1 sheet that will contain all the MNG and SCOM data you want to retrieve. After you press the ‘refresh’ button all the SCOM data will be retrieved and put into a new created worksheet with the given name. You can also use the auto refresh for a , for example , every 1 minute refresh. Now you can make your pivot or graph from the data. The nice part is that you will only have to have excel and this plugin installed. NO SQL connections and NO faulty SQL query’s.

The beating heart “the connection” sheet:

Every row in this sheet contains the MNG connection and the data you want to get. This is done by using the SDK SCOM query language (http://msdn.microsoft.com/en-us/library/bb437603.aspx). In the Type column you specify the type of data you want to receive. For now you can choose : Alert , Objects , Performance.

Now if you press on Refresh every row is processed the MNG group is connected and the data query is executed. The output is written into a new created sheet with the name that you typed into the SheetName column.

If you go to the , for example , MonitoringObjects sheet you will see all SCOM targets with their healthstate. This is because the Query is “HealthState > 0”.

The sheet will look like this:

Next you can use this data as input for everything you want. For example a pivot table that summaries the health of the targets.

Or make an dashboard:

Using Excel with SCOM data this way you can create and display every output need you want. And even better what your boss want. Just give your boss a prepared sheet and let excel do the work. No “he can you give me this today “ or “can you put a other label on this table” ect..

The End

The first nice part is that if you set the auto refresh all the pivot tables used in any sheet is updates also. Imagine that you can create a ‘real time’ excel dashboard and put this full screen on the wall.

The second nice part is that I will SHARE this sheet !! 😉 Just drop me a mail or put a note on this post and I will send it to you. Remember its still a prototype but you can customize it as wish.

This post will be describing the extensions mention in my pervious post about SCOM and Power Pivot:

– Predictive trend line so we can see when the disk space will be 0 in the future.

– Monthly / Weekly / Yearly Aggregations so we can drill in and out of the data. SCOM is only providing daily and hourly aggregations but if you have a lot of data you will have to look at a higher perspective.

– Real data mining so you can detect strange behaviors in your data. [I am out of time this week. Maybe I will blog on this later]

So here we start.

Predictive trend

This one is quite simple to realize. I really love Power Pivot , ill show you!

Select in the graph the line you want to add a predictive forecast to. The data point will be highlighted.

Select in the “Layout” tab the “TrendLine”

Select “Linear Forecast trendline”

Now you will see a trend forecast line. Leave the line selected and now go to the same menu and select “More trendline Options”

In the following menu we set “Forward” to lets say 10. Now the forecast will calculate 10 data points forwards on the X (date) axis.

Next we select “Glow and Soft Edges” because we want to give the trendline forecast some highlighting. Select Presets and take Red.

The result will be:

Cool isn’t it !!

I don’t have a solution to calculate the dates on the x axis so you can see on what date the line will hit 0. But I guess with a macro you could do this. OR by RTM

Monthly / Weekly / Yearly Aggregations

Now comes the part that is most interesting. When a KPI outputs an logical disk measurement it will be written to the OPS DB and to the OPS DWH. Since the DWH will be holding a long time period of this data you can imagine that the size of the DWH DB will grow huge. To solve this the data must be aggregated to periods. If the aggregate process is successful completed the RAW data will be deleted/groomed. This sounds good but not for the performance data, the only default aggregations are Hourly , Daily periods. But what if you want Monthly or even Yearly aggregations. ???? Again Power Pivot to the rescue !

Thanks to a friend of mine , expert in DWH Datamining and also Excel (Albert van Dok), I found the solution. In excel we create a new work sheet. In this sheet, name it “Date Sheet”, we make a Day (dag), Month (mnd) , Year (jaar) column. And fill it with a date range of lets say 2 years. (2011,2012).

Now we link this table to the data table we got from the SCOM DWH. See next steps. Select all the data rows from the A B and C columns. Now we select in the “PowerPivot” tab “Create Linked Table”

The power pivot windows will popup and show you the date range data you selected. Next step is to link the date columns to the date columns from the SCOM logical disk data.

Select in the right corner “Diagram View”

Now we select the DateTime column from the Query table and drag it to the to the dag column in the Table1. The result should be as below.

Next step is to create the Hierarchy calculation in order for getting the correct aggregations.

Select on Table1 all the columns and select right click “Create Hierarchy”

And the result will be :

You may/can rename the default created names.

Now we are going back to the excel sheet. Select the Trend Graph you want to display the monthly aggregation into and minimize in the Field List windows the query tree. And maximize the Table1 tree. Now drag the Jaar and mnd columns to the axis fields and the jaar,mnd,dag columns to the Slicers Horizontal.

The end result will be on the sheet: Selection windows for the year month and day. When you select 1 or more columns in this windows the graph will be recalculated and the aggregated monthly data will be displayed. Look at the x axis. It displays the selected months and the y axis will contain all the AVG diskdata.

Next step is to create a new graph and do the same for the yearly aggregated data. Almost the same as the field selections above but now only the jaar (year) column in the Axis Fields window.

The end result will be:

Now you have created 2 new aggregations for the SCOM data. Good work !!