Splunk Search

Applying conditional to a subset of results?

jamesklassen
Path Finder

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

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Tyr this:

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

jamesklassen
Path Finder

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Customer Survey!

If you use Splunk Observability Cloud, we invite you to share your valuable insights with us through a brief ...

Happy CX Day, Splunk Community!

Happy CX Day, Splunk Community! CX stands for Customer Experience, and today, October 3rd, is CX Day — a ...

.conf23 | Get Your Cybersecurity Defense Analyst Certification in Vegas

We’re excited to announce a new Splunk certification exam being released at .conf23! If you’re going to Las ...