How do I edit my stats search with the proper conditions to return the expected result?

[ronaldsc]

Hello All,

Quite new to Splunk and hoping someone can help point me in the right direction. I've being trying to figure out how to do the following with no luck and at the point where I think I need help.

I am running the following search:

  searchHere   | stats values(sessionid) by srcip, srcport, dstip, dstport, result | WHERE (status=blocked AND status=closed)

I don't seem to get any results with that search. If I run the search without the WHERE command, I get the following back - which in this case is just one session for sample purposes.

    srcip    srcport      dstipdst   dstport   result   values(session)
 192.168.1.10   54319   10.10.10.1  80  blocked 7656319
 192.168.1.10   54319   10.10.10.1  80  closed   7656319

I suspect that the WHERE is not looking at this on a "per session" basis (2 events or entries per session in this case). What I would like to do is find any entries where the result doesn't include both blocked/closed on a per srcip, srcport, dstip, dstport, session grouping basis and have the result shown.

Using the data below here is what I am trying to get results to look like -- shown below:

Sample data:

    192.168.1.10    519 10.10.10.1  80  blocked 7656319
    192.168.1.10    519 10.10.10.1  80  closed  7656319

    192.168.1.20    545 10.10.10.2  80  blocked 3939775
    192.168.1.20    545 10.10.10.2  80  closed  3939775

    192.168.1.20    549 10.10.10.20 80  allowed 5875739
    192.168.1.20    549 10.10.10.20 80  closed  5875739

What I am hoping results look like:

  srcip  srcport      dstipdst   dstport  values(session)    blockedClosed
 192.168.1.10   519 10.10.10.1  80    7656319             Yes
 192.168.1.20   545 10.10.10.2    80      3939775             Yes
 192.168.1.20   549 10.10.10.20   80      5875739             No

In this example, I have an entry where srcip, srcport, dstip, dstport, session grouped together doesn't follow the blocked/closed (or have two entries one that shows blocked the other closed) flow so in this case I'd like it to tell me that a blockedClosed didn't occur for that specific connection. Thanks for the help in advance.

[richgalloway · Accepted solution]

There are a couple of problems with the where clause of your search. The first is the stats command is not passing on a 'status' field. The second is the status field cannot be both blocked and closed at the same time. Moving the where clause before stats will help, but won't get you the results you desire. Try this.

searchHere   | eval blockedClosed=case(status='blocked',"Yes",status='closed',"Yes",1=1,"No") | stats values(sessionid) by srcip, srcport, dstip, dstport, blockedClosed

[ronaldsc]

Thanks, Rich you're a genius. Did the trick.