WalkerCountyTX
asked on
Number of users per half hour graph
We are getting sluggish responses to a certain program used by our organization between certain time frames. I would like to take a report created of when users logged in and out from a date range and graph it to show how many users were logged in either from a half hour stand point to every hour. I have a sample of the information I would like to use below. It is not the exact data of course, but it has the information needed.
I would like the graph to show How many users online per day per hour. Duration does not really matter, I believe. If not per hour, per half hour would work also. My problem seems to be taking usernames and giving them a qualifier so that excel will not count someone twice in the same hour.
Thank You,
Jason Dykstra
UserID Logon Logout Hours Duration
User01 1/1/15 18:54 1/1/15 19:45 0.84 0:50:29
User02 1/2/15 6:09 1/2/15 8:15 2.11 2:06:27
User03 1/2/15 7:18 1/2/15 16:53 9.59 9:35:33
User03 1/2/15 7:58 1/2/15 9:43 1.75 1:45:06
User04 1/2/15 8:03 1/2/15 16:57 8.89 8:53:32
User01 1/2/15 8:08 1/2/15 9:29 1.35 1:21:01
User05 1/2/15 8:18 1/2/15 8:33 0.25 0:15:07
User05 1/2/15 8:37 1/2/15 10:07 1.5 1:30:04
User06 1/2/15 8:49 1/2/15 9:09 0.32 0:19:09
User07 1/2/15 9:06 1/2/15 9:58 0.88 0:52:35
User08 1/2/15 9:15 1/2/15 9:35 0.33 0:19:52
User09 1/2/15 9:38 1/2/15 9:38 0.01 0:00:19
User09 1/2/15 9:39 1/2/15 11:57 2.3 2:18:17
User03 1/2/15 9:55 1/2/15 12:01 2.1 2:05:52
User07 1/2/15 9:58 1/2/15 11:47 1.81 1:48:49
User05 1/2/15 10:10 1/2/15 12:02 1.85 1:51:14
User10 1/2/15 10:38 1/2/15 15:42 5.06 5:03:24
User11 1/2/15 10:46 1/2/15 12:03 1.29 1:17:16
User12 1/2/15 10:54 1/2/15 14:33 3.65 3:38:49
User02 1/2/15 11:00 1/2/15 11:00 0:00:10
User07 1/2/15 11:47 1/2/15 13:44 1.95 1:56:47
User05 1/2/15 13:00 1/2/15 13:20 0.34 0:20:15
User13 1/2/15 13:07 1/2/15 13:28 0.35 0:21:16
User05 1/2/15 13:29 1/2/15 13:36 0.12 0:07:16
User05 1/2/15 13:39 1/2/15 16:56 3.28 3:16:55
User07 1/2/15 13:44 1/2/15 13:52 0.12 0:07:23
User07 1/2/15 14:00 1/2/15 15:54 1.9 1:54:05
User01 1/2/15 14:00 1/2/15 16:25 2.4 2:24:12
User06 1/2/15 14:02 1/2/15 14:03 0.02 0:00:55
User06 1/2/15 14:03 1/2/15 14:04 0.03 0:01:39
User13 1/2/15 14:06 1/2/15 14:10 0.07 0:04:13
User14 1/2/15 14:07 1/2/15 14:35 0.48 0:28:37
User13 1/2/15 15:05 1/2/15 15:57 0.87 0:51:58
User07 1/2/15 16:05 1/2/15 16:08 0.06 0:03:24
User08 1/2/15 16:06 1/2/15 16:12 0.09 0:05:13
User07 1/2/15 16:09 1/2/15 18:44 2.6 2:35:43
User15 1/2/15 16:25 1/2/15 16:42 0.29 0:17:12
User13 1/2/15 16:34 1/2/15 16:36 0.03 0:01:59
User08 1/2/15 16:39 1/2/15 16:40 0.03 0:01:36
User13 1/2/15 16:56 1/2/15 16:56 0.01 0:00:30
User07 1/2/15 18:51 1/2/15 18:52 0.02 0:01:27
User07 1/3/15 18:58 1/3/15 22:14 3.28 3:16:38
User07 1/3/15 22:18 1/3/15 22:20 0.03 0:01:51
User07 1/3/15 22:20 1/3/15 22:23 0.06 0:03:20
User16 1/5/15 6:41 1/5/15 7:23 0.69 0:41:39
User02 1/5/15 6:55 1/5/15 10:40 3.75 3:45:04
User17 1/5/15 7:33 1/5/15 8:02 0.47 0:28:23
User03 1/5/15 7:46 1/5/15 10:49 3.05 3:02:59
User04 1/5/15 7:56 1/5/15 10:41 2.75 2:45:07
User18 1/5/15 8:03 1/5/15 10:48 2.75 2:45:06
User03 1/5/15 8:04 1/5/15 10:49 2.76 2:45:29
User05 1/5/15 8:21 1/5/15 9:21 1 1:00:04
User08 1/5/15 9:04 1/5/15 10:49 1.75 1:45:05
User19 1/5/15 9:10 1/5/15 10:40 1.5 1:30:06
User20 1/5/15 9:14 1/5/15 9:20 0.1 0:06:04
User10 1/5/15 9:17 1/5/15 9:37 0.33 0:19:32
User07 1/5/15 9:20 1/5/15 9:29 0.16 0:09:36
User20 1/5/15 9:20 1/5/15 9:28 0.12 0:07:25
User21 1/5/15 9:26 1/5/15 9:41 0.24 0:14:27
User20 1/5/15 9:28 1/5/15 9:36 0.14 0:08:33
User20 1/5/15 9:36 1/5/15 9:36 0:00:10
User07 1/5/15 9:36 1/5/15 9:39 0.05 0:02:54
User07 1/5/15 9:39 1/5/15 9:41 0.03 0:01:32
User07 1/5/15 9:41 1/5/15 10:07 0.44 0:26:07
User05 1/5/15 9:48 1/5/15 9:53 0.09 0:05:10
User05 1/5/15 9:59 1/5/15 10:44 0.75 0:45:08I would like the graph to show How many users online per day per hour. Duration does not really matter, I believe. If not per hour, per half hour would work also. My problem seems to be taking usernames and giving them a qualifier so that excel will not count someone twice in the same hour.
Thank You,
Jason Dykstra
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad it helped..and sure let me know if you need any help.. :-)
Why would you be discounting when a single user logs on twice in a particular time period?
Surely that is still two login requests which are being slowed by system issues or whatever the problem is. The fact that it happened to be the same person logging back in at a later time is irrelevant. If the second login incidence was a different person you would still include it.
For example, lines 4 & 5 of your sample data are both User03:
He/she logged in at 9:43 for 01:45 hours, logging off at 11:28. He/she then logged in again at 16:53 and stayed logged in for 9:35.
Why should the second login be discounted, it could just as easily have been a different person.
Thanks
Rob H
Surely that is still two login requests which are being slowed by system issues or whatever the problem is. The fact that it happened to be the same person logging back in at a later time is irrelevant. If the second login incidence was a different person you would still include it.
For example, lines 4 & 5 of your sample data are both User03:
He/she logged in at 9:43 for 01:45 hours, logging off at 11:28. He/she then logged in again at 16:53 and stayed logged in for 9:35.
Why should the second login be discounted, it could just as easily have been a different person.
Thanks
Rob H
I take that back, I was misreading the data, at least on that particular example.
User03 logged in at 7:18 for 9:35, logging off at 16:53
In addition there is record of the same user logging in 50 minutes later although they were still logged in according to the above entry.
Thanks
Rob H
User03 logged in at 7:18 for 9:35, logging off at 16:53
In addition there is record of the same user logging in 50 minutes later although they were still logged in according to the above entry.
Thanks
Rob H
ASKER
I still take into account if they are loggins that are far enough apart or more. I do not take into account if the loggins are within the same time frame. For instance, I have a user that logged in at 9:38 and then again shows a log in at 9:39, it will only count that 1 time. I do not know why it is showing up like that in the logs, but for now I do not want to show that as 2 loggins.
On the other side of it, I have a user that logs in at 8:18 and then again at 8:37. In this case I do want to show that as 2 loggins.
On the other side of it, I have a user that logs in at 8:18 and then again at 8:37. In this case I do want to show that as 2 loggins.
That's how it's working in the example that i posted i have made it unique count in 30 Mins interval.. to see the same...
ASKER
Ms. Teotia, I would first like to thank you very much because that is exactly the information I was asking for.
I thought I was good with Excel until I ran into this. I am now being asked if I can show how many users are actually Logged On during specific time frames instead of just showing logins for those time frames.
I see that the data shows me the logon and the logoff and the duration of their session. You do not see the information in the snippet shown, but I am seeing some durations of over 3 days (one was over 80 hours). What is the best option for showing how many users are logged in for each day during that same time frame.
So for instance:
And then it would go on to the next day. If this is too much to ask for please let me know.
I thought I was good with Excel until I ran into this. I am now being asked if I can show how many users are actually Logged On during specific time frames instead of just showing logins for those time frames.
I see that the data shows me the logon and the logoff and the duration of their session. You do not see the information in the snippet shown, but I am seeing some durations of over 3 days (one was over 80 hours). What is the best option for showing how many users are logged in for each day during that same time frame.
So for instance:
Date Time Active
01/01/2015 07:30
08:00
08:30
09:00
09:30
10:00
10:30
11:00
11:30
12:00
12:30
13:00
13:30
14:00
14:30
15:00
15:30
16:00
16:30
17:00
17:30And then it would go on to the next day. If this is too much to ask for please let me know.
I have a question for you will the data you have will be sorted in ascending order only?? In case of multiple logins?
Also can you give me sample data to look at in excel like i updated will develop a solution for you to work.
Saurabh...
Also can you give me sample data to look at in excel like i updated will develop a solution for you to work.
Saurabh...
ASKER
I will work on this after lunch. And I just realized I put a Ms. in the reply above and did not mean to put any qualifiers. I was typing up an IM to someone and I think i got things crossed. Sorry if you are not a Ms. I did not mean to offend.
It's okay buddy i ignored that..at times i do goof up myself when writing to someone in us just looking in their names can be confusing..No offense taken.. :-)
ASKER
Here is an excel sheet that ranges from 01/01/2015 until 01/09/2015. I have broken down the information a little more in more columns, and it has the first solution integrated into it. Columns C&D and H&I break out the Logon and Logoff Date and time into Date in one column and time into another. Columns E&F come from your first solution and round the date and time into 30 minute intervals and then strip off the date and leave just the time.
I also added a new sheet that has some data entry added just in case it helps. I believe that too much information is better than not enough. If you need me to do any more on my side please let me know.
User-Report-Modified-.xlsx
I also added a new sheet that has some data entry added just in case it helps. I believe that too much information is better than not enough. If you need me to do any more on my side please let me know.
User-Report-Modified-.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much, I am going to integrate this into my main sheet and do some data entering. I will keep you informed.
ASKER
This was great, it gave me everything I needed for data. Thank you very much!
I made my second sheet show 1 week of data from 7 AM to 6 PM. Then changed it so you can change the first date and the rest change automatically. Added a row underneath the date that tells you what day of the week that day is as well.
Once again, thank you! This is above and beyond.
I made my second sheet show 1 week of data from 7 AM to 6 PM. Then changed it so you can change the first date and the rest change automatically. Added a row underneath the date that tells you what day of the week that day is as well.
Once again, thank you! This is above and beyond.
Glad it worked.. :-)
Saurabh...
Saurabh...
ASKER