Splunk Search

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

handygecko
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

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

View solution in original post

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

gkanapathy
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

l1bertyx
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

surekhasplunk
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

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

Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...