Creating your own Excel Reports from performance data collected by System Centre Essentials 2010 – Basic Example, Start to Finish

Posted on by

I’ve been using SCE 2010 for a while now and for the most part there have never really been too many demands put on the reports. The built in reports a little quirky and not that flexible but that’s partly by design since you can only use the reports that come with SCE or ones that are already authored in Management Packs, for anything else and you need Ops Manager.

I recently needed to get at the raw data that SCE had collected and do some of my own analysis on a “before and after changes” report so worked out this simple (some may well disagree) method. It will hopefully be useful if you didn’t already know about it and could act as a starting point to create more complex reports.

Steps:

1: Getting the raw data out of System Centre Essentials.

2: Cleaning the data up so Excel can use it.

3. Creating Pivot charts to graph and aggregate the data.

Outputs:

These examples are only guides, once you have the data in your worksheet you may choose to pivot the data in different ways depending on what you want out of it.

1. Time of day CPU usage average graph showing peak average load times

clip_image002

2. 31 day daily average CPU usage showing busy days

clip_image002[6]

Prerequisites for the process are:

1. Excel
Most versions will work but I’m using Excel 2010.

2. NotePad++
Free and open source, download here. A must have replacement for the built in MS notepad. It will be needed to remove certain characters from the raw data but you could write an Excel macro if you’re looking for a little more automation.

3. System Centre Essentials 2010 (or 2007)
Obviously required otherwise there is no data.

4. Performance data retention set in SCE to the maximum of 37 days and this setting in place for 37 days.
Optional but useful if the report is to contain a useful amount of data. A lot of the grouping of the data in Pivot charts later in the process become a little pointless if there is only the default 7 days in there.

1: Getting the raw data out of System Centre Essentials.

Assuming that the you’ve completed the prerequisites getting the raw data out of SCE is surprisingly easy but this method cannot be automated (I’ll do another post automating getting the data out assuming it’s possible) so it’s not a flexible, scalable method that you’d be happy about doing every day if you need to do it for multiple servers and multiple counters.

For this example I’ll be getting the CPU Percent Total for a server over the last 31 days.

  1. Open the SCE Console and click on “Monitoring”
    image
  2. Drill down into the Windows Server node and click on “Operating System Performance” graph
    image
  3. Change the filter options and target the server you are interested in and tick the CPU Percent Total for that server
    image
  4. Change the date range for the graph to the last 31 days and click on OK.
    image
  5. At the top right hand side of the screen click on “Copy data to clipboard”
    image
  6. Open NotePad++ and paste in the information.
    image

Note: If you are accessing the SCE Console over Remote Desktop give the clipboard a few seconds to populate since there can be 60,000 data points or more. You’ll find out if you’ve been too hasty when you paste and nothing is there.

Had you pasted the data straight into Excel you’d find it not formatted neatly into columns as you’d hope. This is one of reasons NotePad++ is needed – to get it into something that Excel can use easily. A macro in Excel could also do the cleanup which I’ll follow on with another post if I end up going down that route.

2: Cleaning the data up so Excel can use it.

Now that you have the data out of SCE you need to clean it up for use in Pivot charts in excel. There are several items that need to be edited and or removed.

The function that we need NotePad++ for is to remove the carriage return line feeds, aka what the “Enter” button does to end a line. NotePad++ can search for and remove them while excel can’t (to my knowledge anyway). NotePad++ identifies CR LF as rn if you enable the Extended Search mode. All the other editing could just as easily be done in Excel since they are simply “find and replace”. At the end of the stage we’ll end up with a clean CSV file for use in Excel.

  1. Press the “show all characters ” button in NotePad++
    image
  2. Delete the line <NewDataSet> at the beginning and </NewDataSet> the end of the file
  3. From the “Search” Menu select “Replace” (or press Ctrl+H)
  4. Change the search mode to “Extended”
  5. Copy <Series_your GUI here> and paste it in the “Find what” field and add rn at the end of the line.
    image
  6. In the “Replace with” field type in your server name and the counter that you used and click on “Replace All”, e.g. “Server 1 CPU Counter”�
    image
  7. Continue find and replace the following:
    1. Replace <X> with nothing (leave “Replace with” field blank)
    2. Replace –04:00</X>rn with nothing (this is the time offset from GMT)
    3. Replace <Y> with nothing
    4. Replace </Y>rn with nothing
    5. Replace T with a single space
    6. Replace with </Series_your GUI here> with nothing
    7. Replace every two spaces with a tab space (“Find What:” press space twice, “Replace with:” t)
      image
  8. Save the file and you’ll have your data ready for excel and graphing.

3. Creating Pivot charts to graph and aggregate the data.

Now that you a csv file which Excel will be happy with you can start the real work of graphing the data in meaningful ways. Most of you reading this will probably not need to follow the next bits but for the sake of completeness and anyone who hasn’t done it before I’ll keep the level of detail covered in the process high.

  1. Open Excel and go to File > Open. Change the files to “All Files (*.*)” and open the text file you just saved
  2. Change the type to “Delimited” and click on “Next”
  3. Tick “treat consecutive delimiters as one” and click on Finish
  4. Insert a new row for the headers, something like “Server”, “Date and Time”, “Value”�
    image
  5. Select all three columns and select Insert > PivotChart and press OK.
    image
  6. Add all three fields to the report.
  7. Move the following fields:
    1. Move “Server” field to Legend Field section
    2. Move “Value” to the Values section
  8. Change the Value Field settings for “Count of Value” to Sum
  9. Right click on the most top left “Date and Time” value and click on Group.
    image

This is the point at which you can group the data in a date group that your want. If you grouped it by Day it would look something like this –

image

Grouped by hour would look like this –

image

And that’s it. It might look a little lengthy the first time but the process is really quite simple.