Splunk Search

Add the count of different values in the same column

JurSolutions
New Member

Hi I'm pretty new to splunk and I learned a lot in the last weeks, while working with it. But I got stuck on a query, where I have a chart with multiple values for the first column, all summed up as count.
So what I have is a chart looking like this:

Provider            Transaction

Vodafone          10

T-Mobile            50

0101                 20

...

Here is my query for this chart:

[search] | stats count by networkOperator | bucket span=1mon _time | rename count as "Transactions" | addtotals col=t row=f label=Total labelfield=networkOperator | rename networkOperator as "Provider"

The problem where I got stuck is the fact, that in this case "Vodafone" and "0101" are the same Provider. Due to different API's the namespace for these Providers can't be changed by us, so I have to deal with these informations.

What I want to do is sum the Transactions of those two rows into one row, called "Vodafone". But I'm failing while trail and error.

My guess was something like this (using eval):

[search] | stats count by networkOperator | bucket span=1mon _time | rename count as "Transactions" | addtotals col=t row=f label=Total labelfield=networkOperator | rename networkOperator as "Provider | eval RealNetOp=case(networkOperator == T-Mobile, "T-Mobile", networkOperator == Vodafone, "Vodafone", networkOperator == 0101, "Vodafone"...)


Maybe this problem is solved very easily and I just failed searching the documentation, but I tried for days now and I can't figure out, what I'm doing wrong.


Thanks for your help in advance!



TJ

Tags (2)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

You can change the value of the field networkOperator for all events that contain such an alias at search time, for example like this:

... | eval networkOperator = if(networkOperator == "0101", "Vodafone", networkOperator) | ...

Another option would be to normalize the names at index time with a SEDCMD, but that can only work well if you know all possible aliases in advance.

View solution in original post

0 Karma

Ledion_Bitincka
Splunk Employee
Splunk Employee

The best way to achieve this would be to use a lookup table that maps the provider name in the data to the real name. The way you've approached this would still work, but you need to quote the values on the right hand side of the == inside case () as in :

| eval RealNetOp=case(networkOperator == "T-Mobile", "T-Mobile", networkOperator == "Vodafone", "Vodafone", networkOperator == "0101", "Vodafone"...)

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

You can change the value of the field networkOperator for all events that contain such an alias at search time, for example like this:

... | eval networkOperator = if(networkOperator == "0101", "Vodafone", networkOperator) | ...

Another option would be to normalize the names at index time with a SEDCMD, but that can only work well if you know all possible aliases in advance.

0 Karma

yannK
Splunk Employee
Splunk Employee

Ledion has the correct one :

[search]
| eval networkOperator=case(
networkOperator= "0101","Vodafone",
1=1,networkOperator
)
| stats count by networkOperator | ...

0 Karma

yannK
Splunk Employee
Splunk Employee

Other example with case, if you have many replacement to do a case, case(condition, value,condition,value....) and do not forget the last condition for the default value.

[search]
| eval networkOperator=case(
"0101","Vodafone",
1=1,networkOperator
)
| stats count by networkOperator | ...

JurSolutions
New Member

That worked thanks!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...