Tag Archives: excel

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: https://michelkamp.wordpress.com/2012/11/25/scom-and-excel-a-perfect-couple/

New features

1) More types to query

The types below can now be used in the Type column:
Events
Objects
Alerts
Performance
TaskResults
Discoveries
Rules
Overrides
Monitors
ManagementPacks

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.

image

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.

https://skydrive.live.com/redir?resid=2FFA0FC5B0B89EED!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:

https://michelkamp.wordpress.com/2012/12/12/your-scom-sdk-query-cheat-sheet/

 

Happy Scomming,

Michel Kamp

Advertisements

Your SCOM SDK Query cheat Sheet

12 Dec

So while  reading the title didn’t you had the feeling that you where back at the college banks… This time I will post a small cheat sheet that you can use in the SCOM excel Workbook is posted last time.

So let’s start.

Using the SDK you have the possibility to get SCOM related data using a sort of SQL query language. Defining a query can be tricky if you don’t know what all possibility’s are. And for sure remember that the key properties are case sensitive !

And please don’t think I found this out my self , all the credits go to the MS Product team at http://msdn.microsoft.com/en-us/library/hh328943.aspx I only wanted to make one page for all.

The query syntax can be found here : http://msdn.microsoft.com/en-us/library/bb437603.aspx (this a 2007 page but is also valid for 2012)

You will see more types of data you can get. I can already tell you that the vnext SCOmExcelWorkbook will be extended with most of the data types shown’ below.

Events

  • Id
  • OriginalId
  • MonitoringObjectId
  • MonitoringClassId
  • MonitoringObjectName
  • MonitoringObjectDisplayName
  • MonitoringObjectPath
  • MonitoringObjectFullName
  • MonitoringRuleId
  • PublisherName
  • Number
  • CategoryId
  • User
  • Channel
  • LevelId
  • LoggingComputer
  • TimeGenerated
  • TimeAdded
  • EventData
  • EventParameters

 

Alerts

  • Id
  • Name
  • Description
  • MonitoringObjectId
  • MonitoringClassId
  • MonitoringObjectName
  • MonitoringObjectDisplayName
  • MonitoringObjectPath
  • MonitoringObjectFullName
  • IsMonitorAlert
  • ProblemId
  • MonitoringRuleId
  • ResolutionState
  • Priority
  • Severity
  • Category
  • Owner
  • ResolvedBy
  • TimeRaised
  • TimeAdded
  • LastModified
  • LastModifiedBy
  • TimeResolved
  • TimeResolutionStateLastModified
  • CustomField1
  • CustomField2
  • CustomField3
  • CustomField4
  • CustomField5
  • CustomField6
  • CustomField7
  • CustomField8
  • CustomField9
  • CustomField10
  • TicketId
  • Context
  • ConnectorId
  • LastModifiedByNonConnector
  • MonitoringObjectInMaintenanceMode
  • MonitoringObjectHealthState
  • ConnectorStatus
  • NetbiosComputerName
  • NetbiosDomainName
  • PrincipalName
  • AlertParams
  • SiteName
  • MaintenanceModeLastModified
  • StateLastModified
  • Management Packs
  • Id
  • Sealed
  • Name
  • FriendlyName
  • Version
  • KeyToken
  • LastModified
  • TimeCreated
  • DisplayName
  • Description
  • VersionId

Performance

  • Id
  • MonitoringObjectId
  • MonitoringClassId
  • MonitoringObjectName
  • MonitoringObjectDisplayName
  • MonitoringObjectPath
  • MonitoringObjectFullName
  • MonitoringRuleId
  • InstanceName
  • ObjectName
  • CounterName
  • HasSignature
  • LearningMonitoringRuleId
  • LastSampledValue

Diagnostics

  • Id
  • Name
  • Accessibility
  • ManagementPackId
  • Enabled
  • TargetMonitoringClassId
  • MonitorId
  • ExecuteOnState
  • Remotable
  • Category
  • Timeout
  • TimeAdded
  • LastModified
  • DisplayName
  • Description
  • HasNonCategoryOverride

Discoveries

  • Id
  • Name
  • Accessibility
  • ManagementPackId
  • Enabled
  • TargetMonitoringClassId
  • ConfirmDelivery
  • Remotable
  • Category
  • Priority
  • TimeAdded
  • LastModified
  • DisplayName
  • Description
  • HasNonCategoryOverride

Rules

  • Id
  • Name
  • ManagementPackId
  • TargetMonitoringClassId
  • Enabled
  • Category
  • DisplayName
  • Description
  • ConfirmDelivery
  • TimeAdded
  • LastModified
  • Remotable
  • Priority
  • DiscardLevel
  • HasNonCategoryOverride

 

Monitors

  • Id
  • Name
  • ManagementPackId
  • Accessibility
  • DisplayName
  • Description
  • TargetMonitoringClassId
  • Algorithm
  • AlgorithmParameter
  • MonitoringRelationshipClassId
  • Category
  • MemberMonitorId
  • ParentMonitorId
  • IsUnitMonitor
  • IsInternalRollupMonitor
  • IsExternalRollupMonitor
  • AlertOnState
  • AlertAutoResolve
  • AlertPriority
  • AlertMessage
  • HasNonCategoryOverride

Recoveries

  • Id
  • Name
  • Accessibility
  • ManagementPackId
  • Enabled
  • TargetMonitoringClassId
  • MonitorId
  • ResetMonitor
  • ExecuteOnState
  • MonitoringDiagnosticId
  • Remotable
  • Category
  • Timeout
  • TimeAdded
  • LastModified
  • DisplayName
  • Description
  • HasNonCategoryOverride

Tasks

  • Id
  • Name
  • ManagementPackId
  • TargetMonitoringClassId
  • Enabled
  • Category
  • DisplayName
  • Description
  • Accessibility
  • Remotable
  • Timeout
  • TimeAdded
  • LastModified

TaskResults

BatchId
ErrorCode
ErrorMessage
Id
LastModified
LocationId
ManagementGroup
ManagementGroupId
Output
ProgressData
ProgressLastModified
ProgressMessage
ProgressValue
RunningAs
Status
StatusLastModified
SubmittedBy
TargetClassId
TargetObjectId
TaskId
TimeFinished
TimeScheduled
TimeStarted

Overrides

  • Id
  • Name
  • ManagementPackId
  • TargetId
  • ContextId
  • ContextObjectId
  • Value
  • Enforced
  • DisplayName
  • Description
  • TimeAdded
  • LastModified

 

Happy SCOMMING

Michel Kamp

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