Join 3,556 readers in helping fund MetaFilter (Hide)


How to get Excel to play nice?
July 4, 2014 6:33 AM   Subscribe

I am trying to generate an excel graph that will give me a date/time overview, and I can't quite crack how to set up my table and chart :( Any help will be greatly appreciated.

I am trying to get an overview of a trend within our IT network, or even to see whether or not any trend exists. I have 6 servers, and every once in a while *something* happens. We keep logs of date&time that this did happen, but because of the MASSIVE input of data (multiple gigs per hour) we do not keep verbose logs. I am trying to plan for a short window when we do turn on verbose logging, so i'd like to see if there is a trend in when the event happens.

So, how to generate a graph that will show a trend of 6 servers when you have date & time? Do you map this out for each server at a time and then try to overlay? what should my axis be? which chart would be the correct one to use?

Ideally I could look at this/these graph(s) and say "hmmm, OK, there's a good likelihood that if we have verbose logging from 8-9am we can catch this pesky bug".

Thank you in advance for any help and suggestions :)
posted by alchemist to Computers & Internet (6 answers total)
 
also, this event can happen at multiple times in 1 day, but only 1 server at a time. So, you can have:

4-july 10:30 - server 01
4-july 11:00 - server 01
4-july 12:11 - server 02
5-july 02:15 - server 06
posted by alchemist at 6:42 AM on July 4


I'd do by setting up three columns. One is for the server name, the other is for the date, the last is for the hour of the event [=HOUR(A2)]. Once you have all the data in this format, generate a pivot table with the server name and hour as row labels, and the date as a column label. It's not a graph, but it will show you where you have spikes.

If you really need a graph, you could manipulate the pivot table data into a format that you could then graph.
posted by Gorgik at 6:43 AM on July 4


A pivot table is a good idea and, as noted, will allow you to create a graph from it.

When you give the sample data, does that appear all in one cell? Or is it properly separated into a date cell, time cell, and server name cell? If it is not properly sorted into individual cells then you will have to manipulate the data prior to making the pivot table and graph.

You can do this fairly easily with formulas, using =LEFT(), =MID, and =RIGHT()....
posted by dfriedman at 6:53 AM on July 4


One other way to put the data into the correct cells would be to use the column splitter tool that Excel provides: Data --> Text to Columns.
posted by dfriedman at 6:55 AM on July 4


I like the direction Gorgik is going. You could also use COUNTIF to count the number of problems within certain time segments if you set up your data as he suggests.

Then you could have a table with the times and a number of how many incidents occurred in that timeframe. Like this:

0700 3
0800 2
0900 1
etc.

If you're interested primarily in the time of day, you could do a line chart with each server as a different line and the times of the day on the x axis using Gorgik's layout. This would be more specific than the list above, and would be better at showing, for example, if 8:15 - 9:15 were the best time than the list above. This won't differentiate between days, though, if the issue might be more likely on a Tuesday or something.
posted by jeoc at 6:59 AM on July 4


Thanks for the input Gorgik! i'll give that a try and report back :) sorry to thread-sit but i'm so excited!
posted by alchemist at 7:18 AM on July 4


« Older Asking for a friend. Can y...   |  We'll be on the Cape near Trur... Newer »

You are not logged in, either login or create an account to post comments