1

I know there is a very similar thread to the this one already – Excel to count number of concurrent sessions based on start/end times – however, as much as I try, I am unable to replicate and manipulate the suggested answer to meet my needs.

I am also looking to detect when a user ID is logged onto a legacy unix ERP system from multiple PCs simultaneously – potentially they’re sharing their credentials with a colleague.

An example of the data is:

Record #    Time In    Time Out    User      PC Name
1           10:54      10:55       Al        PC1
2           10:55      16:55       Dave      PC42
3           09:11      09:29       Al        PC1
4           11:02      18:03       Al        PC1
5           16:19      17:38       George    PC678
6           16:43      17:41       Al        PC678
7           16:46      17:39       Dave      PC42
8           17:25      17:49       Al        PC42

The “Time In” and “Time Out” values are actually full date/times, so you don’t need to worry about comparing events that happened on different days.  I am displaying only the times here so as to avoid providing Too Much Information.  The above data can be shown graphically as:

    bar chart

Towards the end of the list (and the bottom of the chart) you can see that user Al is accessing the system from multiple PCs at the same time, as well as the other users also accessing the system from the same PCs. User Al is either walking round and logging in (unlikely) or the other users are able to log in as Al in order to perform tasks their permissions do not allow them to do. Ideally I'd like to have a new column to highlight that at that time there were concurrent sessions from User ID Al on different PCs. Record # 4, 6 and 8 would alert – Record # 2 and 7 are OK as the same user logged in on the same PC.

  • Your data are flawed: (1) DEF is logged in to PC345 for two overlapping time periods (rows 2 and 7), (2) ABC and DEF are logged in to PC345 concurrently (rows 2 and 8), and (3) ABC and GHI are logged in to PC456 concurrently (rows 5 and 6). If it’s possible and valid for a user to be logged to a single PC for overlapping time periods (i.e., he logs in, then he logs in again, and then he logs out, and later logs out again) please explain more clearly how your data work and what results you want. … (Cont’d) – Scott Jul 5 '16 at 2:03
  • (Cont’d) …  As if your raw data weren’t hard enough to understand, you then say that you want rows 4, 6 and 6 flagged. 4, 6 and 6!  (I guess you mean 4, 6 and 8?)  Also, it might not matter in this case, but it’s conventional, when asking a question about Excel, to say what version of Excel you are using.  Please do not respond in comments; if you choose to make your question clearer and more complete, edit it. – Scott Jul 5 '16 at 2:03
  • Your data hurt my eyes.  I’ve changed them in a trivial way to be more eye-friendly.  (By the way, is that meant to be yy/mm/dd format, or is it dd/mm/yy?) – Scott Jul 5 '16 at 2:04
  • Thanks all for the posts. Sorry it wasn't explained very well and my raw data was somewhat difficult to read (first post so still learning). Date format was dd/mm/yy. Just to confirm this is the log file from a legacy unix system running - so it would be possible to open two sessions as Per Dave in row#7 from the same PC or node( a user is free to open as many sessions as they want - I know poor design). We have a fix to prevent user opening multiple sessions from their own pc and most importantly having two sessions open with their credentials on different nodes - I've been asked to.... – MikeH Jul 5 '16 at 7:41
  • analyse the data so we can identify users who do login from multiple nodes and understand their need to do it - e.g. are their permissions wrong? – MikeH Jul 5 '16 at 7:42
0

You want to identify any rows for which there is at least one other row with the same User ID for which the In/Out time ranges overlap.  This is fairly straightforward (at least, once you know the answer.  The first step is to figure out that interval Start1/End1 overlaps interval Start2/End2 if and only if Start1 < End2 and End1 > Start2.  (This is easy to see if you think about it; easier if you draw it.)  So, for each row, we want to count the rows in the login/logout record for which the above is true, and the User ID equals the User ID for this row.  That is simply

=COUNTIFS(A$2:A$9, "<"&B2, B$2:B$9, ">"&A2, C$2:C$9, "="&C2)

for your original data layout (where Time In is Column A, Time Out is Column B, and User is Column C).  This will evaluate to a minimum of 1, because every row overlaps itself.  You want to see which rows overlap other rows, so we do

=IF(COUNTIFS(B$2:B$9, "<"&C2, C$2:C$9, ">"&B2, D$2:D$9, "="&D2) > 1,  "Overlap",  "")

Once I change the Time In for Record #7 (User Dave on PC42) from 16:46 to 16:56 to eliminate the overlap with Record #2 (also User Dave on PC42), which I presume was unintentional, I get the following results:

        spreadsheet

which, I believe, are what you want.

OK, if it’s possible and valid for a user to be logged to a single PC for overlapping time periods (as Dave does on PC42 in records 2 and 7), and you don’t want these to be counted as overlaps, then we need to modify our formula to count only rows where the PC is different:

=IF(COUNTIFS(A$2:A$9, "<"&B2, B$2:B$9, ">"&A2, C$2:C$9, "="&C2, D$2:D$9, "<>"&D2) > 0, "Overlap", "")

Note that we have to change the test from > 1 to > 0 because rows no longer overlap themselves.

  • By the way, I don’t understand that other question either.  Luckily, I believe that I (mostly) understand this one. – Scott Jul 5 '16 at 2:04
  • Thanks all for the posts. Sorry it wasn't explained very well and my raw data was somewhat difficult to read (first post so still learning). – MikeH Jul 5 '16 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.