CODEWITHSUNDEEP
sqlsql-server
user38858
user38858

Reputation: 316

How can I get the most recent different event?

Here is what mytable looks like:

+-----------------+-----------+-------------+------------------+
|      Time       | EventType | MachineName | UserDisplayName  |
+-----------------+-----------+-------------+------------------+
| 7/22/2014 6:52  | CONNECTED | MACH-10     | alice.brown      |
| 7/22/2014 6:52  | PENDING   | MACH-10     | alice.brown      |
| 7/22/2014 9:09  | PENDING   | MACH-2      | mike.hensworth   |
| 7/22/2014 6:58  | CONNECTED | MACH-2      | mike.hensworth   |
| 7/22/2014 6:57  | PENDING   | MACH-2      | mike.hensworth   |
| 7/22/2014 7:00  | CONNECTED | MACH-3      | will.garden      |
| 7/22/2014 6:59  | PENDING   | MACH-3      | will.garden      |
| 7/22/2014 5:30  | PENDING   | MACH-3      | will.garden      |
| 7/22/2014 11:20 | CONNECTED | MACH-4      | kent.clark.admin |
| 7/22/2014 11:20 | PENDING   | MACH-4      | kent.clark.admin |
| 7/22/2014 9:28  | CONNECTED | MACH-5      | inigo.montoya    |
| 7/22/2014 9:28  | PENDING   | MACH-5      | inigo.montoya    |
| 7/22/2014 9:15  | PENDING   | MACH-123    | inigo.montoya    |
| 7/22/2014 12:37 | CONNECTED | P-002       | joseph.smith     |
| 7/22/2014 12:36 | PENDING   | P-002       | joseph.smith     |
| 7/22/2014 9:43  | CONNECTED | P-005       | penny.pennyworth |
| 7/22/2014 9:41  | PENDING   | P-005       | penny.pennyworth |
| 7/22/2014 5:41  | CONNECTED | P-006       | thor.zeus        |
| 7/22/2014 5:40  | PENDING   | P-006       | thor.zeus        |
+-----------------+-----------+-------------+------------------+

The situation is people (UserDisplayName) are logging into machines (MachineNames). Eventtype PENDING is the start of the logging in, EventType CONNECTED is successful connection.

Essentially, I need the average amount of time it takes a user to log into a machine. I don't have a good way to connect the PENDING status to the CONNECTED status, so I'm trying to calculate the time difference between the CONNECTED status and the most recent PENDING EventType for a given MachineName.

I don't know how to do that, and that's what this question is about.

But I'm also having problems I'd like to overcome and identify.

  1. (For example, inigo.montoya) Sometimes users start a PENDING on one machine, then give up when it takes to long, and start a second PENDING on a second machine. There's no CONNECTED listed to close that first PENDING event. I'd like to identify these occurrences.

  2. (For example, MACH-3) Sometimes PENDING never becomes CONNECTED on the machine, and somehow the users are able to start a second PENDING which works as intended. I'd like to identify these occurrences.

Upvotes: 0

Views: 46

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13233

I think this should do what you want:

with successes as
 (select x.userdisplayname,
         x.machinename,
         x.time as connection_time,
         y.time as pending_time,
         datediff(mi, y.time, x.time) as mins_between
    from mytable x
    join mytable y
      on x.userdisplayname = y.userdisplayname
     and x.machinename = y.machinename
   where x.eventtype = 'CONNECTED'
     and y.eventtype = 'PENDING'
     and y.time = (select max(z.time)
                     from mytable z
                    where z.userdisplayname = y.userdisplayname
                      and z.machinename = y.machinename
                      and z.time < y.time))
select y.userdisplayname, y.machinename, null, y.time, null
  from mytable y
  left join successes s
    on y.userdisplayname = s.userdisplayname
   and s.machinename = y.machinename
   and y.time = s.pending_time
 where s.connection_time is null
   and y.eventtype = 'PENDING'
union all
select * from successes

Note that the rows having nulls represent failed connection attempts.

Upvotes: 1

Related Questions