Applying conditional to a subset of results?

[jamesklassen]

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.

Any help is appreciated!

Session Data:
_Time.......Server.........User-----------State----------------Data
5pm.........server1.......user3------Disconnected---blahblahblah
4pm.........server1.......user1------Disconnected---blahblahblah
3pm.........server2.......user1------Connected-------blahblahblah
3pm.........server1.......user2------Disconnected---blahblahblah
2pm.........server3.......user1------Disconnected---blahblahblah
12pm.......server1.......user2------Disconnected---blahblahblah

What I'd like returned:
5pm.........server1.......user3------Disconnected---blahblahblah
3pm.........server1.......user2------Disconnected---blahblahblah

[manjunathmeti]

Tyr this:

<your search> | stats values(State) as State, latest(*) as * by User | where NOT match(State, "Connected")

[jamesklassen]

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.

[kamlesh_vaghela]

@jamesklassen

I think it should work. Check this.

| makeresults count=3 | eval diff=100 | accum diff | eval _time= _time + diff, User="user2",Server="Server".(diff/100), State="Disconnected",Data="blahblahblah" | append [ | makeresults count=3 | eval diff=300 | accum diff | eval _time= _time + diff, User="user3",Server="Server".(diff/300), State="Disconnected",Data="blahblahblah" ] | append [| makeresults count=1 | eval _time= _time + 450| eval User="user1",Server="Server2", State="Connected",Data="blahblahblah"] | sort  _time | table _time Server User State Data | stats latest(_time) as _time latest(*) as *  by User | where State="Disconnected"

Can you please share more details about in which scenario it is not working?? You can enhance your provided data set also for same.