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!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...