See the dataset below. Ultimately (this is part of an inner join with another search) I'd like to return the the latest result for any user who does not have an active connection.
So for the data below, user2 and user3 do not have "Connected" sessions, and so I'd like to return their most recent result. User1 on the other hand, does have a "Connected" session, so I would not like to return his result.
I'm not sure if I'm overthinking this, but it seems I need to group each users' results and if any of their State=Connected then throw them out? This seems to be getting messed up when a user has an active session, then opens a new one to a different server, and then closes their first session. Originally I was using a |dedup to get their latest connection attempt, but the case I described breaks this.
Unfortunately this doesn't work. If we return the latest result for each user, then we can't consider cases where they're still connected to a session that was opened before their last one and which is still open.