All posts by Ashley Feldon-Lawrence

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

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.


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.


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


2. 31 day daily average CPU usage showing busy days


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

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++
  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.
  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”
  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)
  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”
  5. Select all three columns and select Insert > PivotChart and press OK.
  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.

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 –


Grouped by hour would look like this –


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

Training: 44CO175 – SharePoint 2010 Administrator Bootcamp

44CO175 – SharePoint 2010 Administrator Bootcamp (BPIO)

Step-by-step understanding is key to successfully implementing and deploying SharePoint 2010. This 15-module course will guide you through each critical stage, giving you exactly the skills you need to leverage full value from the latest SharePoint technology.

Module 0 – Getting Ready for a SharePoint 2010 Implementation: Why Governance and Business Requirements are Essential for a Successful Implementation.
Module 1 – Understanding the Architecture of SharePoint Server 2010
Module 2 – Installing SharePoint Server 2010.
Module 3 – Administrating and Configuring Farm Settings in SharePoint Server 2010
Module 4 – Understanding and Administrating Web applications
Module 5 – Managing Web Applications
Module 6 – Introduction to Site Collections
Module 7 – Creating Site Collections
Module 8 – Managing Site Collections
Module 9 – Working with SharePoint Server 2010 Portals
Module 10 – Governance and Information Assurance
Module 11 – Enterprise Content Types & Managed Metadata
Module 12 – Managing Documents and Records
Module 13 – Workflow
Module 14 – Implementing and Managing Search
Module 15 – People & Social Networking
Module 16 – Disaster Recovery

A handful of notes from the week..

Installation Preparation for Service & Install Accounts 

The SharePoint Installation Account

While I’ve become used to installing applications on servers with either the local or domain admin account, SharePoint 2010 really really really needs to have its own account created for the installation process. If you don’t you’ll have to correct the various service and app pool accounts later and that’s a pain.

Key points about the account used to install with –

  • It will become the application pool account used in IIS for the Central Admin
  • If you do plan to let SharePoint create your databases (content DBs’ etc…) this account needs rights to the SQL database to create DB’s (DBCreator and DBAdmin)
  • If DB’s are already created then it only needs DBAdmin
  • It only needs Local Admin permissions on the installation machine
  • Not to be used in day-to-day admin
  • Imagine this account to be “enterprise admin” of SharePoint.
  • It should never be used again after the initial install, obviously that doesn’t mean disable or delete it.

Example User Account – spinstall

The Farm Admin Account

 The farm admin account is an account that should be used by as few people as possible.

  • It either needs to be a local admin on the SharePoint front end or create a new supper user or login as spinstall(logging in as spinstall is not recommended)
  • The farm admin account change services running on the machine hence the need for local admin rights, farm admin right in SharePoint central admin isn’t enough

Example User Account – spfarmadmin

During the SharePoint 2010 install

 Make sure you move index location during install off from the C:…14data location

 This index file is a flat file used in search and can grow very large in next to no time.

After the install – the Configuration Wizard

Do not use it, it doesn’t follow best practice.

 Performance Tip for SharePoint databases 

Turn off Auto Growth

While it’s been mentioned a thousand times and shouldn’t need to be mentioned…

To prevent it happening in existing installations for any new databases:

SQL Management  Studio > Database > Model. Under the file groups section  change the properties for .mdf to grow by 50MB at a time

Fixing it for existing databases:

Open the properties of each database and under the file groups section, change the properties for .mdf to grow by 50MB at a time

Note: Why 50MB? Because it’s just right for SharePoint since it aligns with the default maximum upload file size.

Registering Managed Service Accounts for SharePoint 2010

  • Best practice is to add them here first then start to use them to run services
  • All are generally fine  as just domain user accounts without elevated privileges

Application Pool Accounts in IIS for SharePoint 2010

 Never change the app pool account from inside IIS as the config DB will not know about the change, web apps will not know about the change and new servers added to the farm will not know which account to use.

One of the few things you can and do need to configure in IIS is SSL certificates and also needs to be done on each and every web front end.