hide all comments


Viewing server logs in Excel

March 15, 2005 15:28:52 +0200 (EET)

The other day I wanted to check that our web server had been up and network connectivity OK through the day. One quick way of doing this is to glance through the WWW logs for any obvious gaps in time. Since that's boring and error-prone with big logs, I imported the day's log into Excel. Actually, I copy-pasted the contents, since that way Excel handles separating the data into columns automatically, without having to go through the import wizard.

Unfortunately, Excel doesn't allow graphing time data sensibly. If say there are nine entries at 1am and one at 11pm, it will show ten peaks evenly spaced throughout the day. Since I knew I'd seen time data spread out correctly in a graph before, I woke up the Office Assistant 'Clippy', who shook off the dust and actually answered my question right first time: Excel graphs date data correctly, but not time data.

Since time is relative, that gives us a chance. I added a new column, with values calculated from the time value*365*24, formatted as dates. This mapped the day's time data over 24 years, one hour per year. Graphing that with bytes-sent gave an instant view of the data, and after a little formatting as below, I ended up with the following: no real gaps, other than the early hours of the morning when most of our customers are asleep. Like I should be now...

WWW activity

Format details: Choose a 2D column view. Set the minimum and maximum for the X axis scale to 1.1.1900 and 31.12.1923, the base unit to days, major unit to years, and minor unit to months. Each major unit thus represents one hour, and minor unit 5 minutes. Change the number to a custom format showing just yy, i.e. the two-digit hour. For the data series, set the border to none and the area to black, and on the Options page set the gap width to 0.