Add the count of different values in the same column

[JurSolutions]

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

[Ledion_Bitincka]

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"...)

[martin_mueller · Accepted solution]

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.

[yannK]

Ledion has the correct one :

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


[yannK]

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]

That worked thanks!