Using Excel 2010 to Identify Tasks that Took Too Long from a Log File

OK typically I am using the quick & dirty SMSTrace32.exe for viewing log files…nice clean interface, and highlights errors in red, and also works in WinPE. Simple. In this case I’m looking at a scanstate log file from USMT 4.0. Trace32 can be found in System Center Configuration Manager 2007 Toolkit V2 here http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=5a47b972-95d2-46b1-ab14-5d0cbce54eb8

image

 

But there is no way to filter by the amount of time each specific task took, and in this case I’m suspecting somewhere deeply embedded among the 1.04 million lines in this log file, that one or two tasks are taking a really long time. So I’m going to open the log file in Excel, where we can do some processing on the data. I used that data preview to separate out some of the fields… By the way…Excel 2003 has a limit of 65,000 rows, so could not handle such a large log file. Although Excel 2007 introduced million+ row support, the increase caused some performance and rendering issues when working with graphics objects in the new regions of the larger grid. Excel 2010 handles this much better, so there is not much lag, even on the 32-bit version of Excel 2010.

image

So once it was imported I created a new row to give some headers to my log file, then selected the entire Time column, right clicked on it and chose Format Cells… set it to Time

image

 

Now I insert a new column which I called TimeDiff. Starting with the 2nd row of data (row 3) I put in the formula =B3-B2

image

 

Now to get it into the 0:00 format I have to select column C, right click and choose Format Cells… and select custom h:mm:ss

image

You can then apply the formula to the whole column by selecting the cell with the formula, selecting column C, deselecting the header Cell by hitting Ctrl+Click then hit Ctrl+V to paste it in (you’ll have to add header back again). Now I turn on a filter…and look what I find:

Two tasks took over 10 minutes to run. (the 12 hr ones, I think that’s a mistake, but anyway, we’ll check it out)

image

 

So I find the culprits of the delay:

image

Looking at the size of these files:

USMT.MIG – 5 GB

RZC6.NSF – 3.4 GB

And keep in mind they are backing up to network in this case, plus USMT compression & encryption…in any case, mystery solved. And I didn’t have to read all 1.04 millions of the log file

About chentiangemalc

specializes in end-user computing technologies. disclaimer 1) use at your own risk. test any solution in your environment. if you do not understand the impact/consequences of what you're doing please stop, and ask advice from somebody who does. 2) views are my own at the time of posting and do not necessarily represent my current view or the view of my employer and family members/relatives. 3) over the years Microsoft/Citrix/VMWare have given me a few free shirts, pens, paper notebooks/etc. despite these gifts i will try to remain unbiased.
This entry was posted in Excel and tagged . Bookmark the permalink.

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