topic Re: Comparing Values Based on Another Field in Splunk Search

Comparing Values Based on Another Field

genesiusj — Mon, 13 Apr 2020 16:52:51 GMT

Hello,
I'm thinking is real simple, but I have been digging in the weeds for so long I am unable to see this simple answer.

Field Names: File, Method
Field Values: File - A, B, C Method - Success, Fail
Counting the number of events by File/Method combination
Searching for File(s) where the count of Fail is greater than the count of Success.

| stats count(status) AS numEvents BY file, statusMsg
| sort numEvents
| stats list(statusMsg) AS "HTTP Method", list(numEvents) AS "Events by Method", sum(numEvents) AS sumEvents BY file
| rename file AS "File Name", sumEvents AS "Events by File"
| sort -"Events by File"

...

This is a file we don't want in our results.
badNum.doc  
500 Internal Server Error     12
200 Success                  149

This is a file we do want in our results.
UPDATE_NEW.doc                  
200 Success                  116
500 Internal Server Error    475

Thanks and God bless,
Genesius

Re: Comparing Values Based on Another Field

jpolvino — Mon, 13 Apr 2020 17:12:32 GMT

Here is one way to do it, using Run Anywhere SPL:

| makeresults
| eval raw="File=A,Method=Fail File=A,Method=Success File=A,Method=Fail File=B,Method=File File=B,Method=Success File=C,Method=Fail File=C,Method=Success File=C,Method=Fail File=A,Method=Success File=A,Method=Success File=B,Method=Fail"
| makemv raw | mvexpand raw | rename raw AS _raw | kv
| table File Method
| stats count AS Vol by File,Method
| xyseries File Method Vol
| where Fail>Success

Re: Comparing Values Based on Another Field

genesiusj — Mon, 13 Apr 2020 17:23:51 GMT

@jpolvino
Thanks for the quick response.
My post was very generic/minimal. We have over 70 Files. And the number of methods covers all of the HTTP methods: 200 range, 300 range, 400 range, and 500 range.
Thanks and God bless,
Genesius

Re: Comparing Values Based on Another Field

jpolvino — Mon, 13 Apr 2020 17:28:54 GMT

Ok then, you'll have to use a new "status" field. Here is an example where 200 is defined as "Pass" and everything else is "Fail"

| makeresults
| eval raw="File=A,Method=200 File=A,Method=300 File=A,Method=400 File=B,Method=500 File=B,Method=200 File=C,Method=300 File=C,Method=500 File=C,Method=500 File=A,Method=500 File=A,Method=200 File=B,Method=400"
| makemv raw | mvexpand raw | rename raw AS _raw | kv
| table File Method
| eval status=if(Method==200,"Pass","Fail")
| stats count AS Vol by File,status
| xyseries File status Vol
| where Fail>Pass