Help with graphing log data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I am trying to create a chart or graph that shows how many users are
concurrently logged into an application on our network. I would like to be
able to show this by day, week and month to indentify trends and high load
times. For the actual data I get a log file that shows USERNAME, LOGIN,
LOGOUT for each session in three columns that I can import into Excel. LOGIN
and LOGOUT are in the format Date and Time (example: 12/1/2004 1:01:01 PM)

If anyone can assist or make some suggestions on where to start it would be
much appriciated!
 
Petrov,

Possibly you can start with something like this . . .

Assuming "Frank" is in cell A3 and the entire range covers cells A1:E8 . .

Login Login Logout Logout
Date Time Date Time
Frank 1/1/2005 6:00 1/1/2005 9:00
Jim 1/2/2005 8:00 1/2/2005 15:00
Bill 1/2/2005 9:00 1/2/2005 12:00
John 1/3/2005 23:00 1/4/2005 6:00
Kim 1/4/2005 4:00 1/4/2005 14:00
James 1/5/2005 22:00 1/6/2005 4:00

1/2/2005 2 <- Users on 01/02/2005

Assuming the date is in cell A11, the formula to count the number of users
on 01/02/2005 is:

=COUNTIF(B3:B8,"="&A11)

1/1/2005
1/3/2005 4 <- Users between 01/01/2005 and 01/03/2005
Assuming the start date is in cell A13 and the end date is in cell A14, the
formula to count the number of users in the range is:

=SUMPRODUCT((B3:B8>=A13)*(B3:B8<=A14))

You can use this formula to count weeks, months, etc. Graph you data as per
the conditions you set in your formulas.

This MS Knowledgebase article provides info on how to perform time
calculations:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q214094
 
John - thanks that gives me something to start with! I do need to modify this
somehow to show concurrent use as opposed to total use or logins - ie what
was the peak number of people logged in to the system at the same time on
that date? So somehow I need the results to reflect the logout time as well
as the login.
 
Back
Top