SCOM Meets Excel PowerPivot

10 Mar

 

In this post I will share some ideas I had a long time but never had time to realize it. It is just a showcase in the future I will of course extend this to a real production version.

The idea:

I wanted to do data mining on performance data that System Center Operations Manager SCOM has collected. So a data analyst could look at the data , do real-time / interactive actions to the data view perspectives and use forecasting to find quickly the bottlenecks to solve. Again a great predictive approach for the availability increment of your IT environment!

Solution(s):

Some solutions for this came up in my mind:

writing my own SQL query’s to do this.

Disadvantage: complex to maintain and not end user (data analyst) friendly

writing my own SQL reports to do this.

So combine the SQL query’s and use the output to render a report.

Disadvantage: In most cases a data analyst wants to change the perspectives at runtime. A report is static and needs to be rerendered every time and this is time consuming.

– Performance Point !

Yhea that sounds good. A few years ago Microsoft it self posted a demo to use performance point and SCOM DWH to do this. ( BI – Dashboard Integration )

Disadvantage: O course I tried this , It works but it is really hard to configure and extend and for sure if you aren’t a expert in maintaining SharePoint , SQL analyze services and OLAP.

Hmm … then I found the magic word: “PowerPivot”

Powerpivot is a really good tool for a data annalist. The pivot table views can be changed real-time and also aggregations can be changed on the fly. Since PowerPivot integrates smoothly with SharePoint it would mean the end user (data annalist) won’t have to install a client site software. Only a internet explorer is needed. (The SharePoint part I will blog in a future post.)

So the solution to try out was found. PowerPivot it will be….

PowerPivot:

What do you need to start:

1) operational SCOM 2007/2012 environment with the DWH role enabled.

2) Office 2010 with excel installed on your analyst end-user computer

3) PowerPivot for Excel 2010 plugin installed on your analyst end-user computer. ( http://www.microsoft.com/downloads/nl-nl/details.aspx?FamilyID=4b5d8276-d8c8-49b3-a653-f09f744ed9c5 )

4) SQL server account to access the SCOM DWH database and read tables.

If you have done all of this you are ready to start:

Filling the Pivot Table

Open Excel and create a new work book. I am not going to show you a screenshot of it Knipogende emoticon

Now notice the new powerpivot tab in the top bar. If not then you have to you google bing to solve this. Hint: add-inn security.

image

Click on the PowerPivot and select the icon “PowerPivot”

image

 

Now the powerpivot tool will be opened in a separate window.

In the Home Tab select “Get External Data” and “From SQL Server”

SNAGHTML6bc0d31c

Now we must specify the SQL server connection to the SCOM DWH. Fill in the correct SQL server instance/Database name and credentials

SNAGHTML6bc46fe8

Next steps will be something you will have to follow me blind on. For now I am not going in to deep on explaining the details.

We are going to add the SQL query we want to use for getting the data

SNAGHTML6bc79f22

before we know what to past in this window we must make the SQL query.

Open SQL management studio connect to the SCOM DWH and run the query below:

select displayname , ManagedEntityRowId from vManagedEntity
where displayname like ‘Windows Server 2008 Computer Group%’

Now the result is the instance id of the scom group “Windows Server 2008 Computer Group” that contains all the Windows 2008 computers targets.

image

Next we want to get the performance data from the logical disk concerning:

Logical Disk Free Megabytes 2008

and

% Logical Disk Free Space 2008

We have to find the rule GIUDs for this. Run the query:

select * from dbo.vRule where
rulesystemName like ‘Microsoft.Windows.Server.2008.LogicalDisk.%’

image

Select the 2 highlighted GUIDs. This are the performance rules that are collecting the wanted disk data.

Now we combine this in the query below:

exec Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceReportDataGet
@StartDate=‘2012-03-5 09:10:00:000’
,@EndDate=‘2012-03-9 09:10:00:000’
,@OptionList=N'<Data><Values><Value>59667
<Rule>B49F6325-F53D-9309-C133-2C73653CA29F</Rule><Color>63,63,255</Color><Type>Line</Type>
<Scale>1</Scale></Value><Value>59667
<Rule>F01B368F-AFB9-7AA2-0C80-2F9C924790B8</Rule><Color>63,63,255</Color><Type>Line</Type>
<Scale>1</Scale></Value></Values></Data>’
,@DataAggregation=0

Change the @startDate and @EndDate to the period you want to get the data. Test the query to see if it gives output.

image

 

So now you have the query we can use in the PowerPivot. Copy and past it in to this window.

SNAGHTML6be12df9

Press Save and the query will be executed and the results will be displayed.

image

Next we have to create some Calculated fields , otherwise we can’t add this to the Value  pane window in the graph.

Go select the AverageValue column. And select in the “Home Tab” > “AutoSum”> “Average”

image

Now you have created a calculated field. Do the same for all other values you want to display in a graph.

image

At this point we have a pivottable with value data!

Pivot to Graph

Next we are going to create the trend graphs for the annalist. In the PowerPivot windows Press the PivotTable Icon > “Two Charts (Vertical)”

image

Now there will be in excel created 3 sheets. Two sheets containing the data from the pivot table and 1 sheet containing the 2 graphs. I rename the sheets to better names:

image

Now we are going to configure the graphs. Select in Excel the Graph Sheet (diskspace Analyze). Click on the top graph. On the right the field select windows will be shown. Select the fields show in the picture below and drag it to the correct pane on the bottom. (click on the screenshot to enlarge)

image

Some short explanation on what you just did:
The field in the Axis Fields is the X axis and shows the date times. The field dropped in the Values pane will display on the Y axis, this will be the disk space value. This are the 2 most important fields. But…. what if a computer has 2 logical disks then the values will be added to one line instead of 2 lines. So we need to get categories configured. This is done by the fields in the Legend pane. So now we have a graph but it shows all the data from the 2 performance rules. That not really interactive, we want to select the rules runtime. So we drag the RuleDisplayName into the Slicers vertical pane and whala we see our interactive selection list. We do the same for the interactive selection of the computers. Drag the Path field into the Slicers Horizontal pane.

Last we play with the Graph styles. Select the Graph and then change the style.

image

Select the type “Line with markers”

SNAGHTML72618c31

Next we change the looks of the graph. Select the graph and play with the Layout options.

image

The result will be:

image

Cool it is isn’t it ???? Now you can select the logical disks interactively and the graph will be rebuild. 

To be continued…

Because this post is becoming to long I will post in my next blog about some extras a data annalist has to have.
– 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.

Below a teaser.. Knipogende emoticon

image

 

I hope you see how easy it is to use PowerPivot to analyze your SCOM data.

If you want the excel source file you will have to leave a message below  Knipogende emoticon and maybe it will share it …

Happy Scomming,

Michel Kamp

Advertisements

19 Responses to “SCOM Meets Excel PowerPivot”

  1. Per June 12, 2012 at 12:02 #

    Hello Michel!

    I really like this post, however, when testing the queries the sp don’t return any data, are there any documentation around to help me in debuggin that?

    /Per

  2. JohnB December 11, 2012 at 17:00 #

    I get the same results; no data is returned. Any help?

    • Michel Kamp December 11, 2012 at 18:32 #

      Hi,

      I would advice to use the scomexcel workbook for data retrieval. See my latest post under the tag ‘excel’

      Michel

      Verzonden met mijn Windows Phone ________________________________

      • JohnB December 12, 2012 at 15:51 #

        Well, I am not interested in doing this simply as an exercise. I’m looking for something that will do forecasting for disk space usage. So your other post is of no use.
        You’ve gotten 2 replies here to this blog saying they got no data. Do you have any suggestions on how to troubleshoot that?

      • Michel Kamp December 12, 2012 at 16:15 #

        Hi,

        You can get the same results with my scom excel workbook. But if you want a longer data period, and i think you want to, then you will have to you the dwh data. Okay. Check the ‘ in the query. Could be corrupted during copy. If not check the dates used. Other post your query and i will have a look.

        Michel

        Verzonden met mijn Windows Phone ________________________________

      • JohnB December 12, 2012 at 17:33 #

        I would like to go back one year.
        Here is the query. My Managed Entity ID was different than yours.

        exec
        Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceReportDataGet
        @StartDate=’2011-12-11 08:00:00:000′
        ,@EndDate=’2012-12-11 08:00:00:000′
        ,@OptionList=N’745
        B49F6325-F53D-9309-C133-2C73653CA29F63,63,255Line
        1745
        F01B368F-AFB9-7AA2-0C80-2F9C924790B863,63,255Line
        1′
        ,@DataAggregation=0

      • Michel Kamp December 12, 2012 at 20:47 #

        I see youre optionlist is incorrect. I miss the XML elements.
        See example below. Use the vManagedEntity table to get the Contaiment target. And the vrule table for the perf rule. See the blog post for the exact querys.

        O yea , keep in mind that aggerationtype of 0 is RAW data. I think that this has a rentention of 90days. Better would be to use 20 (hour) or 30 (day). Check yoour retentions table for this.

        exec Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceReportDataGet
        @StartDate='2012-03-5 09:10:00:000'
        ,@EndDate='2012-03-9 09:10:00:000'
        ,@OptionList=N'
        B49F6325-F53D-9309-C133-2C73653CA29F63,63,255Line
        1
        F01B368F-AFB9-7AA2-0C80-2F9C924790B863,63,255Line
        1'
        ,@DataAggregation=0

        Michel

      • Michel Kamp December 12, 2012 at 20:57 #

        o i see the wordpress engine is deleteing the xml elements. i have enclosed it in the code tags.. don’t know what is happend.
        if you are sure you have picked the correct contaiment target and rule guid. Then it could be the aggeration type. But i guess that it is in the contaiment.
        You could email it to me directly. michel027 at the microsoft hotmail with extention .com (for the spammers detections)
        michel

  3. sri December 11, 2012 at 23:11 #

    hi can you please share excel source file for “SCOM Meets Excel PowerPivot”… master.srikanthreddy@gmail.com

  4. zaidi December 18, 2012 at 13:45 #

    Whats wrong with my SCOM…

    Select displayname , ManagedEntityRowId from vManagedEntity
    where displayname like ‘Windows Server 2008 Computer Group%’

    — Does not return anything, however

    Select displayname , ManagedEntityRowId from vManagedEntity
    where displayname like ‘%Windows server 2008%’

    — Returns

    Microsoft Windows Server 2008 R2 Standard 148
    Microsoft Windows Server 2008 R2 Standard 213

    —————- Also

    Select * from dbo.vRule where
    rulesystemName like ‘Microsoft.Windows.Server.2008.LogicalDisk.%’

    – returns nothing.

    Whats should I Do ?, Please Help.

    • Michel Kamp December 18, 2012 at 14:24 #

      Hi,

      There’s nothing wrong with scom. The query is the problem. Look at the like. If you don’t use a leading % then it only returns the records with a starting exactly what you filled in.

      Michel

      Verzonden met mijn Windows Phone ________________________________

  5. zaidi December 18, 2012 at 16:00 #

    Actually I cant see any Logical Disk Counters in VRule…

  6. ManuCool April 11, 2013 at 10:28 #

    Hi Michel,

    Great post.
    I’m running into a problem when adding the query in powerpivot.
    The query runs fine in SQL Management Studio.
    However when adding it to PowerPivot, I get the error “The SQL statement is not valid. There are no columns detected in the statement.”
    If I design the query in the wizard, it returns data. I have done a bit of research and it seems that the store procedure Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceReportDataGet will return the error in powerpivot because of SET NOCOUNT ON statement in the store proc.

    Thus I wonder how you managed to get the data in powerpivot.

    Thanks
    Manu

  7. sjap April 12, 2013 at 23:49 #

    Where can I find the Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceReportDataGet stored procedure?

    • Michel Kamp April 13, 2013 at 07:15 #

      Hi,

      You can find this storedprocedure in the DWH database.

      Michel

      Verzonden met mijn Windows Phone ________________________________

  8. Markus Klein December 11, 2013 at 16:13 #

    Hi Michel, did you ever get a chance to do the Part 2 of this post?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: