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
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!!!
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!
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.
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!!!
rbechtold's answer also worked, but your post was first so I'll accept this as the answer.
... | stats count by fruit
...
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