News Stay informed about the latest enterprise technology news and product updates.

Exporting Performance Monitor log data to Microsoft Excel

Step-by-Step Guide: Exchange Server capacity planning with Performance Monitor -- part 5 of 6.

By exporting the Performance Monitor log data to Microsoft Excel, you can compare collected data on a week by week...

basis to look for long-term trends. You can even use Excel's graphing feature to display graphs of the values of individual counters.

When viewed through Notepad, the raw data you collect will look similar to what you see in Figure B. When you open your .CSV file in Microsoft Excel, it will look like Figure C.

Figure B: This is what the raw collected data looks like.
Figure B

Figure C: This is what the data looks like when viewed through Excel.
Figure C

Since we are specifically interested in long-term trends, I recommend taking an average of each collected value and then pasting that average into a separate Excel spreadsheet that contains averages for other weeks.

For example, in Figure C, you will see that Column B contains the Memory \ Available Bytes counter. The first recorded value is in Row 2 and the data goes to Row 114 (your results will vary). As such, I can put a formula in Cell B116 that computes the averages of everything in Column B. In this case, the formula would look like: @AVERAGE(B2..B114).

You can then copy this formula and paste it into the other columns along this row. Doing so will calculate the averages for each column. In the case of the Excel spreadsheet shown in Figure C (and most likely for your spreadsheet too), you should avoid averaging Column A, because it simply contains the time. Likewise, averages should start with Row 2, not Row 1, because it contains column headers, not real data.

Figure D shows what the calculated averages will look like.

Figure D: Take an average for each column.
Figure D

Once you have taken your average values for the week, place them into a spreadsheet that also contains the averages from other weeks. This will allow you to graphically view Performance Monitor data over a long duration.

Figure E is an Excel graph based on the Memory \ Available Bytes counter. For the purpose of this example graph, I used data collected every few seconds, but the exact same concept can be applied to data collected over a longer period.

Figure E: This is what it looks like when you graph Performance Monitor data through Excel.
Figure E

If I had actually made this graph with weekly averages, I could have set it up so the axis at the bottom of the graph indicated the date the average was taken. It would be unusual for a graph with a collection of weekly averages to have the sudden and long-lasting drop that is shown in this graph -- unless something major had happened with the system and additional RAM was later added to the server.

A graph of weekly averages would more often look like the data that's shown after the drop. It would vary from week to week, but would show a definite trend over time.

It's this trend that is important to understand. If, for example, you noticed that the average Memory \ Available bytes was gradually dropping over time (even if some weeks showed an increase in average available memory), you could use that information to predict when your server's current memory might become inadequate, and use that prediction to justify additional Exchange Server hardware acquisitions.


STEP-BY-STEP GUIDE: EXCHANGE CAPACITY PLANNING WITH PERFORMANCE MONITOR

Home: Introduction
Step 1: An introduction to Performance Monitor as a trend-analysis tool 
Step 2: Configuring Performance Monitor for Exchange Server
Step 3: Reading and analyzing Performance Monitor logs for Exchange Server
Step 4: Viewing a Performance Monitor log data summary report
Step 5: Exporting Performance Monitor log data to Microsoft Excel
Step 6: Related links from SearchExchange.com
 

ABOUT THE AUTHOR:   Brien M. Posey, MCSE, is a Microsoft Most Valuable Professional for his work with Exchange Server, and has previously received Microsoft's MVP award for Windows Server and Internet Information Server (IIS). Brien has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. As a freelance technical writer, Brien has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit Brien's personal Web site at http://www.brienposey.com.

Dig Deeper on Microsoft Exchange Server Monitoring and Logging

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

SearchEnterpriseDesktop

SearchCloudComputing

SearchSQLServer

Close