How to count the number of different values (multivalue) in a field?

[russell120]

I have a multivalue field with at least 3 different combinations of values. See Example.CSV below (the 2 "apple orange" is a multivalue, not a single value. Its delimited by a newline, "apple" is actually stacked atop of "orange"):

container        fruit
  15           apple orange
  18           apple orange
  3              apple 
  5              orange
  44             orange
  66             orange

What query do I need to produce this table:

apple_and_orange    just_apple    just_orange
        2                1            3

[rbechtold]

Hey Russell,

If the intention is to treat the "apple orange" multivalue fields as a unique fruit type, we'll need to remove convert them to a non multivalue field. This can be done in a few different ways, but for this example I'll use the nomv command.

Once that is done, we can then run a stats count by fruit and it should produce the results we want, just improperly formatted.

The "rex mode=sed" portion isn't nessesary, but I end up using it to replace any multivalue fields with an "and" breaker for later formatting.

Then, we'll simply use the transpose command to use our "fruit" column values as our new data headers.

Lastly we'll use the rename command to add the string "just_" to all of our field names.

I'm providing some fake data so you can play around with it to see how it works here (copy paste this into any Splunk instance):

|makeresults count=20
| eval container = random()%70
| eval fruit = random()%3
| eval fruit = if(fruit = 0, "apple,orange", if(fruit = 1, "apple", if(fruit = 2, "orange", "tomato?")))
| eval fruit = split(fruit, ",")
| nomv fruit
| stats count by fruit
| rex field=fruit mode=sed "s/\n/_and_/g"
| transpose header_field=fruit
| fields - column
| rename * AS just_*

Nomv Command Documentation: https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Nomv
Transpose Command Documentation: https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Transpose
Rex Command Documentation (see sed expression header): https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/rex

Let me know if anything doesn't make sense, or it isn't working for you and I'm happy to help!

[russell120]

Your answer solved my issue, but I accepted jaime.ramirez's answer as the solution since he posted first. With your answer, I learned what the nomv command is/does and I thank you for that. It will make a number of panels I've made that much more simpler.

[jaime_ramirez · Accepted solution]

Hi

You could try the following:

| makeresults
| eval events="15 - apple orange|18 - apple orange|3 - apple|5 - orange|44 - orange|66 - orange"
| eval events=split(events, "|")
| mvexpand events
| eval events=split(events, " - ")
| eval container=mvindex(events, 0)
| eval fruit=mvindex(events, 1)
| eval fruit=split(fruit, " ")
| fields container fruit
| fields - _time
| rename COMMENT as "--- Sample Generated Data Above ---"
| rename COMMENT as "--- Query Below ---"
| eval fruit=mvjoin(fruit, "_and_")
| stats count by fruit
| transpose header_field=fruit

Hope it helps!!!

[russell120]

rbechtold's answer also worked, but your post was first so I'll accept this as the answer.

[adonio]

... | stats count by fruit ...

[russell120]

Tried that, doesn't work unfortunately. It doesn't count the number of the multivalue value, which is apple orange (delimited by a newline. So in my data one is above the other). The result of your suggestion is:

count  fruit
 1   apple
 3   orange