Stats aggregation with potentially a eval-where clause is ideal

[thulasikrishnan]

I am trying to work a set of data that looks like this:

I want to display it like so:

My problem is getting the mv list of failed sessionIds. I wish we had something like stats count(eval(status_code=200)) as success_count values(sessionId(eval(where(status_code>200)))) as failed_sessionId by req_method

Appreciate any suggestions to achieve this with reasonable simplicity.

[kamlesh_vaghela · Accepted solution]

@thulasikrishnan

Can you please try this?

YOUR_SEARCH | eval failed_sessionId=if(status_code>200,sessionId,NULL) | stats values(failed_sessionId) as failed_sessionIds count(eval(status_code=200)) as success_count by req_method | nomv failed_sessionIds | rename req_method as method

My Sample Search:

| makeresults | eval _raw="sessionId    req_method  res_method  status_code
1   checkRequest    checkResponse   503
1   provisionRequest    provisionResponse   200
8   checkRequest    checkResponse   200
8   provisionRequest    provisionResponse   200
f   checkRequest    checkResponse   503
f   submitRequest   submitResponse  503
5   checkRequest    checkResponse   200
5   provisionRequest    provisionResponse   503
" | multikv | table sessionId req_method res_method status_code | eval failed_sessionId=if(status_code>200,sessionId,NULL) | stats values(failed_sessionId) as failed_sessionIds count(eval(status_code=200)) as success_count by req_method | nomv failed_sessionIds | rename req_method as method

Thanks

[thulasikrishnan]

This looks like its doing its job. This is good! I have accepted the answer. thanks @kamlesh_vaghela

Now I am gonna have to try to figure how it scales i.e. when there are 100s of failed sessionIds and I only need to take 5 or 10 sample values (for users to troubleshoot) in the failed_sessionIds field. If you have ideas on how to achieve that on top of this solution, appreciate if you could give it a shot. But this is good enough too.

[kamlesh_vaghela]

@thulasikrishnan

You can do it. Just following below actions.
- Remove search portion | nomv failed_sessionIds from existing search.
- Add new search portion to that search. | eval mySample=mvindex(failed_sessionIds,0,2)
- Add again removed search portion to the search | nomv failed_sessionIds
Here I have used mvindex for taking the first 3 events. You can change the 3rd parameter in mvindex with 5 or 10 value as per your need.

https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/MultivalueEvalFunctions#mvindex.2...

Finally. your search will look like.

| makeresults 
| eval _raw="sessionId    req_method    res_method    status_code
 1    checkRequest    checkResponse    503
 1    provisionRequest    provisionResponse    200
 8    checkRequest    checkResponse    200
 8    provisionRequest    provisionResponse    200
 f    checkRequest    checkResponse    503
 f    submitRequest    submitResponse    503
 e    checkRequest    checkResponse    503
 e    submitRequest    submitResponse    503
 5    checkRequest    checkResponse    200
 5    provisionRequest    provisionResponse    503
 " 
| multikv 
| table sessionId req_method res_method status_code 
| eval failed_sessionId=if(status_code>200,sessionId,NULL) 
| stats values(failed_sessionId) as failed_sessionIds count(eval(status_code=200)) as success_count by req_method 
| rename req_method as method 
| eval mySample=mvindex(failed_sessionIds,0,2) | nomv failed_sessionIds

I hope this will help you.

Happy Splunking

[thulasikrishnan]

Thanks! Appreciate all your help @kamlesh_vaghela . Have a good weekend ahead!