topic Re: How to count the same values over different fields together? in Splunk Search

How to count the same values over different fields together?

RonWonkers — Wed, 19 Jun 2024 16:49:10 GMT

Lets say we have the following data set:

Fruit_ID Fruit_1 Fruit_2 1 Apple NULL 2 Apple NULL 3 Apple NULL 4 Orange NULL 5 Orange NULL 6 Orange NULL 7 Apple Orange 8 Apple Orange 9 Apple Orange 10 Apple Orange

Now I am trying to count the total amount of every fruit, in the above example it should be 7 apples and 7 oranges, the problem is that these fruits are seperated in 2 different columns because a fruit name can be both an apple AND an orange, how do I deal with this when counting the total amount of fruit?

Counting one at a time works:

| stats count by Fruit_1

But how do I count both to give a total number since they are 2 seperate columns

I tried combining both columns so its all in 1 long list of values in 1 column but I could not get a definitive answer on how to do this. I tried appending results so first count Fruit_1, then append count Fruit_2 but I did not get the right result of Apple: 7 Orange: 7. Its either 1 or the other.

Does anybody have a fix for how to count over multiple fields like this and combine the result together in 1 field?

Re: How to count the same values over different fields together?

RonWonkers — Wed, 19 Jun 2024 14:34:33 GMT

For context: this question is regarding use cases/user stories for Splunk.

A use case can be linked to multiple user stories, and I want to count the amount of total user stories.

Re: How to count the same values over different fields together?

RonWonkers — Wed, 19 Jun 2024 23:47:26 GMT

A little update:

I now got to a point where I have the following situation:

Fruit_1 count Fruit_2 count Apple 5 null null Orange 10 null null Pear 5 Apple 5 Melon 10 Orange 10

How do I get it so that the amount of apples and oranges from column Fruit_1 and Fruit_2 are combined into 1 big fruit list and 1 count list? So the result should be: Fruit Count Apple 10 Orange 20 Pear 5 Melon 5

Re: How to count the same values over different fields together?

ITWhisperer — Wed, 19 Jun 2024 17:27:47 GMT

| eval fruit=mvappend(fruit1,if(fruit2!="NULL",fruit2,null())) | stats count by fruit

Re: How to count the same values over different fields together?

RonWonkers — Wed, 19 Jun 2024 17:31:11 GMT

Thank you! Just like that it works and only in 1 line 🙂