Lab Utilization 1

[kernal]

I have a csv file (using crystal 9). My csv file has numerous records (one has up to 4215 records and goes from 09/03/2009 to 01/08/2010).

ACTION COMPUTER ID DATE TIME
log-on 123 1 Thu 09/03/2009 11:03:17.96
log-off 123 1 Thu 09/03/2009 12:01:04.65

log-on 23 2 Thu 09/03/2009 12:19:55.52
log-off 23 2 Thu 09/03/2009 12:32:05.25

log-on 5 3 Fri 09/04/2009 20:36:59.51
log-off 5 3 Fri 09/04/2009 21:02:41.68


User wants to know the following and I have no idea even how to begin.

1. What is the estimate of the peak utilization during a typical day? Use a percentage.

2. How many hours during a typical day are you at peak usage?

I hope I have given you all of the information needed. Help is very much appreciated!!!

 

[kernal]

I'm hoping with what I have done with the data that someone can now help me (I think I have gone in the right direction so far).

1. I now have records that have the logon and logoff date and times instead of 1 record having the logon date times and then next record having the logoff date times (thanks to one of my co-workers who used a different program).

2. I have the data counting IDs in 10 minute intervals per day (it seems to be working). I created a times table with 10 minute intervals that I joined to the data. I can't find the post where I got this idea so I could thank the person with a star but it had something like alldaytimes table being joined >= starttime and <=endtime.

3. The data now looks like the following in crystal:

date times # of logins
01/05/2010 11:10:00 1
01/05/2010 11:20:00 1
01/05/2010 11:30:00 1
01/05/2010 11:40:00 1
01/05/2010 11:50:00 1
01/05/2010 12:00:00 1
01/05/2010 12:10:00 1
01/05/2010 12:20:00 1
01/05/2010 12:30:00 1

01/07/2010 15:30:00 1
01/07/2010 15:40:00 1
01/07/2010 15:50:00 1
01/07/2010 16:00:00 1
01/07/2010 16:10:00 1
01/07/2010 16:20:00 1
01/07/2010 16:30:00 1
01/07/2010 16:40:00 19
01/07/2010 17:10:00 1
01/07/2010 17:20:00 1
01/07/2010 17:40:00 1

01/08/2010 09:50:00 1
01/08/2010 10:00:00 1
01/08/2010 10:10:00 1
01/08/2010 10:20:00 1
01/08/2010 10:30:00 1
01/08/2010 10:40:00 1
01/08/2010 10:50:00 1
01/08/2010 11:00:00 1
01/08/2010 11:10:00 1
01/08/2010 11:20:00 1
01/08/2010 11:30:00 1
01/08/2010 11:40:00 2
01/08/2010 11:50:00 2
01/08/2010 12:00:00 1
01/08/2010 12:10:00 1
01/08/2010 12:20:00 1
01/08/2010 15:00:00 1
01/08/2010 15:10:00 5
01/08/2010 15:20:00 1
01/08/2010 15:30:00 1
01/08/2010 15:40:00 1
01/08/2010 15:50:00 1
01/08/2010 16:00:00 1
01/08/2010 16:10:00 1
01/08/2010 16:20:00 18
01/08/2010 16:30:00 19

01/11/2010 14:00:00 1
01/11/2010 14:10:00 1
01/11/2010 14:20:00 1
01/11/2010 14:30:00 1
01/11/2010 14:40:00 1
01/11/2010 17:50:00 1
01/11/2010 18:00:00 1
01/11/2010 18:10:00 1

01/12/2010 14:30:00 1
01/12/2010 14:40:00 1
01/12/2010 14:50:00 1
01/12/2010 15:00:00 1
01/12/2010 15:10:00 1
01/12/2010 15:20:00 1
01/12/2010 15:30:00 1
01/12/2010 15:40:00 1
01/12/2010 15:50:00 1
01/12/2010 16:20:00 1
01/12/2010 16:30:00 18
01/12/2010 16:40:00 1
01/12/2010 16:50:00 1
01/12/2010 17:00:00 1
01/12/2010 17:10:00 1
01/12/2010 17:20:00 1
01/12/2010 17:30:00 1
01/12/2010 17:40:00 1
01/12/2010 17:50:00 1
01/12/2010 18:00:00 1
01/12/2010 18:10:00 2
01/12/2010 18:20:00 3
01/12/2010 18:30:00 5
01/12/2010 18:40:00 4
01/12/2010 18:50:00 4
01/12/2010 19:00:00 2
01/12/2010 19:10:00 2
01/12/2010 19:20:00 2
01/12/2010 19:30:00 2
01/12/2010 19:40:00 2
01/12/2010 19:50:00 2
01/12/2010 20:00:00 1
01/12/2010 20:10:00 1
01/12/2010 20:20:00 1
01/12/2010 20:30:00 1
01/12/2010 20:40:00 1
01/12/2010 20:50:00 1

Is it now possible to get the following information? If yes then what would be the next steps?

1. What is the estimate of the peak utilization during a typical day? Use a percentage.

2. How many hours during a typical day are you at peak usage?

I really appreciate your help!

 

[lbass]

I think you need to get clarification from the person requesting the report. Percentage of what? Distinct count of users per day? Or percentage of the sum of counts per interval? What interval? What do they mean by peak usage? It sounds like they are thinking about a range of values, so what range? From your data, it doesn't look like peak usage occurs for more than 10 or 20 minutes, so what do they mean by how many hours? Etc.

If you don't get clear answers, you can't really write a report that addresses the questions they are raising.

-LB

 

[kernal]

Thank you lbass for helping me and now I know what questions, I need to ask the person. The person won't be back until next week so I'll post an update as soon as I know more.

 

[kernal]

I spoke to the person wanting this data and they were ok with just giving them a line chart for each day. Thanks lbass. I'm still going to keep your questions handy, in case, the person wants this type of data in the future.