Splunk Search

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

russell120
Communicator

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
0 Karma
1 Solution

jaime_ramirez
Communicator

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!!!

View solution in original post

rbechtold
Communicator

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
Communicator

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
Communicator

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
Communicator

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

adonio
Ultra Champion

... | stats count by fruit ...

0 Karma

russell120
Communicator

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