SCOM and Excel a perfect couple.

25 Nov

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.

SNAGHTML45b6974d

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.

image

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:

SNAGHTML45eed5d4

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

SNAGHTML45c240d5

Or make an dashboard:

SNAGHTML45d65c8f

 

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.

Happy Scomming.

Michel Kamp

Advertisements

60 Responses to “SCOM and Excel a perfect couple.”

  1. M.Mathew November 26, 2012 at 14:54 #

    This in intresting stuff.Thx for posting it. Could you please send me the details to mmats@hotmail.com

    • Matt February 24, 2014 at 14:28 #

      I would love a copy! This is excatly what we are wanting to do with our monitoring boards please send krozgrov@hotmail.com

  2. Drew V.O. November 26, 2012 at 22:28 #

    I’ve been wanting a way for customers with OpsMgr operator role to self serve when those repeat questions are asked. Never considered using Excel through the SDK. Think you’re my hero today.

    • Drew V.O. November 27, 2012 at 16:49 #

      Forgot to mention that I’d love a copy too

  3. Marnix Wolf November 28, 2012 at 07:57 #

    Hi Michel.
    This is something special, awesome. Can you send me the spreadsheet?

    Cheers,
    Marnix

  4. Michael November 28, 2012 at 08:22 #

    Hi Michel.
    This is interesting, and I really would like to have a copy.

    Thanks,
    Michael

  5. Rajiv Panchal November 28, 2012 at 10:19 #

    Hi Michel,

    This is great stuff! Can you please send me copy of this excel sheet?

    Thanks & Regards,
    Rajiv

  6. Marc November 28, 2012 at 10:43 #

    Hi Michel,

    sounds great!
    Plz send me the sheet, too.

    Thanks,
    Marc

  7. Andrew November 28, 2012 at 13:37 #

    Hello
    This looks good, can you send me a copy please
    Thanks Andrew

  8. Peter Forster November 28, 2012 at 14:07 #

    Hi,
    sounds great – can I have a copy too?
    -Peter

  9. Alexander November 28, 2012 at 14:17 #

    Hi,
    Super stuff!
    May I also have a copy?

    Thanks a bunch!
    Alex

  10. John November 28, 2012 at 16:56 #

    Good work here, Michel. I would love a copy. Thanks.

  11. Roland De Clerck November 28, 2012 at 17:55 #

    Hallo, looks very promising. Can you send me a copy ? Thanks

  12. Warren Kahn November 29, 2012 at 06:39 #

    Great work, please send it to me aswell. Thanks.

  13. Casper Krogh November 29, 2012 at 10:52 #

    Hi Michael,

    We would like to export SLA dashboard data in Excel. Did you experiment with this, or/and do you think it could be done?

    • Casper Krogh November 29, 2012 at 10:53 #

      .. Regardless, I would like a copy of it as well.

  14. jaimebraz November 29, 2012 at 11:29 #

    Hi Michael,
    Amazing work.
    Could you send me a copy please?

  15. Vincent November 29, 2012 at 14:07 #

    Wow. Nice. I can make some nice dashs for some people here.
    Can you send me this excel file please?

  16. Joe November 29, 2012 at 14:32 #

    My boss will love this. He is an Excel junkie. Please send me a copy.

  17. CMCalvin November 29, 2012 at 16:43 #

    I’d like to take a peek at this.

  18. Sameer November 29, 2012 at 20:41 #

    This is just fantabulous. Would love to have a copy to test it. Can you please email it to me?

    Thanks
    Sameer

  19. John November 30, 2012 at 00:05 #

    Great stuff can i check it out?

    Thanks-John D

  20. Anonymous November 30, 2012 at 15:46 #

    Hi Michel could you send me sheet. Excellent job…thanks

  21. sri December 3, 2012 at 07:58 #

    please send me the copy of the excel sheet to sugunacan@hotmail.com

    Sri

  22. Scott Reading December 3, 2012 at 23:36 #

    Thanks for your work. Please send a copy of the excel sheet to scottr@micrsoft.com.

  23. Sergey December 5, 2012 at 19:10 #

    Hi, this thing could significantly simplify my SCOM investigations, great work! Could you send me your example to s.gonch@hotmail.com? Thanks.

    • Ruud_K December 10, 2012 at 14:33 #

      looks nice, this could simplify my SCOM tasks. Can you sent me a copy? ruud_k@hotmail.com

  24. Anderson Green December 10, 2012 at 15:10 #

    Ohh! A real time excel dashboard, sound great to me! Thumbs up for your nice invention!

  25. sri December 11, 2012 at 22:43 #

    Sounds interesting and keep up the good work. can you please share a copy with me? …
    master.srikanthreddy@gmail.com

  26. Jason December 12, 2012 at 16:31 #

    Michel, this is very interesting! Would you be able to share the spreadsheet with me please?

  27. rob boulter December 13, 2012 at 12:53 #

    Hi, Great use of Excel. Plz send spreadsheet. Many thanks.

  28. Henrik Nørgaard Hansen December 13, 2012 at 15:24 #

    SCOM and PivotTables; you’ve gotta love it. Me too!

  29. Erdem GULHAN December 17, 2012 at 22:30 #

    Wow great work! I want a piece of that too. Thank you.

  30. Anonymous December 18, 2012 at 05:42 #

    Hey Michel, This is great stuff, I would like to have a copy as well. Please send me at mathewcleetus@gmail.com.. Happy Holidays..

  31. Jack December 24, 2012 at 12:13 #

    This is brilliant! Could you possibly send me a copy to jack.elwell74@gmail.com ?

    Thanks!

  32. khikyni January 16, 2013 at 15:00 #

    Great Idea!! Could you send me a copy?
    Keep Going!!
    khikyni at yahoo.es

    • Greg Hatch January 16, 2013 at 19:50 #

      Awesome idea — Microsoft should send you a Win8 RT tablet in thanks and add this sheet to their SCOM blog. Please send me the file too!

  33. Carlos February 4, 2013 at 19:24 #

    Very very good idea!! could you please sende a copy at carles24@hotmail.com

    Regards.

    • Michel Kamp February 4, 2013 at 19:36 #

      Hi,

      See my last post on release 2 of the workbook. This post also includes a direct download link

      Michel

      Verzonden met mijn Windows Phone ________________________________

  34. cocktail dress for a pear shaped body June 18, 2013 at 13:05 #

    You are my inhalation, I have few blogs and sometimes run out from post kalibugan.xlogzwp-includesimagessmiliesicon_smile” .

  35. Aref October 1, 2013 at 08:16 #

    Hi
    Sounds great.
    Can I have a copy?
    Tnx alot
    aref.ghiasi@live.com

  36. Anonymous October 3, 2013 at 14:00 #

    I would love a copy of this as well. My Address is: semikolin@gmail.com

  37. Rob February 7, 2014 at 09:34 #

    That is so cool, please may I have a copy of it please.

  38. Anonymous August 4, 2014 at 15:33 #

    Hi Michel..
    Can you please share me this sheet to pradeep.pakanati@gmail.com

    • Michel Kamp August 7, 2014 at 19:46 #

      Hi,

      Please Look for part 2 on my blog. This will have a download link.

      Michel

      Verzonden met mijn Windows Phone ________________________________

  39. Daniel V October 17, 2014 at 21:17 #

    I would definitely like a copy of this!

  40. Marius March 5, 2015 at 09:28 #

    Hi Michel, Is this spreadsheet still something you are giving away? I would greatly Appreciate a copy. – marius

  41. leo December 15, 2015 at 15:17 #

    Hi, i am Server Administrator in training from germany. I would love to get a copy of that sheet.

    Sincerely
    Leo
    leo.khayet{q}yahoo.de

  42. Jonathan Pritchard March 22, 2016 at 11:35 #

    Many thanks for sharing your work Michael. Excel/PowerBI really does appear to be *the* flexible way to translate SCOM data into a reporting language that pleases the management chain 🙂

  43. Nilesh September 12, 2017 at 07:19 #

    Hello Michael,

    Excel looks really great and can use in my environment can you send me the excel on my email ID Plz.

Trackbacks/Pingbacks

  1. Opsmgr – Excel! | System Center PT - November 29, 2012

    […] o Michel Kamp ou pedir o envio do ficheiro através dos comentários no blog dele, através deste link. Aproveitem que é de […]

  2. Touchdown : ScomExcelWorkbook V2 is released « Touching SCOM - January 28, 2013

    […] After some delays I have finished the V2 version of the ScomExcelWorkbook. See old  V1 post here: https://michelkamp.wordpress.com/2012/11/25/scom-and-excel-a-perfect-couple/ […]

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: