Archive

Conditionals - is there a way in splunk to sum field A if Field B does NOT contain a specific word?

Explorer

I'm new to splunk and it's a little over my head. Please forgive me. I loaded data from a csv file into splunk. The csv file contains two column headers--one text and one numerical.

Column A - "John", "Jack", "Tom", "John"
Column B - 9, 7, 5, 3

Is it possible in splunk (or a third party method) to sum field A where Field B does NOT contain a specific word? Using the example above, sum all the fields in Column B where Column A is NOT equal to "John." If yes, please provide some concrete examples or at least a push in the right direction would be much appreciated.

Tags (1)
0 Karma
1 Solution

Splunk Employee
Splunk Employee
source=myfile.csv | where A!="John" | stats sum(B)

View solution in original post

Splunk Employee
Splunk Employee
source=myfile.csv | where A!="John" | stats sum(B)

View solution in original post

Splunk Employee
Splunk Employee

one more thing. If you need multiple sums, you can do:

source=myfile.csv
| stats sum(eval(if(A!="John",B,null()))) as sumifnotjohn
        sum(eval(if(A=="John",B,null()))) as sumifjohn
        sum(B) as totalsum

Engager

I also second what @surekhasplunk has commented. Instead of (A=="John"), use (A=="John*") for the case when you have John1, John2, John3..... and so on in Column A. It (eval) doesn't seem to accept wildcards, at least when I attempted this approach.

0 Karma

Communicator

How can i use wild card character in this scenario when i just have a search pattern like incident name INC* in place of John

0 Karma

Explorer

Thank you! This answered my question and many others. I also discovered--for anyone who may read this later--that 'where' can be omitted:

source=myfile.csv A!="John" | stats sum(B)

and wildcards can be used:

source=myfile.csv | where A!="Jo*" | stats sum(B)

Thanks again for the help.