Splunk Search
Highlighted

How to search the count of each value in a multivalue field, and display the corresponding total in a table?

Path Finder

Hey Fellow Splunkers

I would like to total multiple values for the same fields.

field="Fruits"

Within this field, I have multiple values:

Values:
-Oranges = 10
-Apples = 3
-Grapes = 90
-Pears = 4

index="random" host="xxxxxx" Fruits=*"Apple" |stats count(Fruits)

I would like to count each value within the field and output the content in a table with its corresponding total.

Thank You,

0 Karma
Highlighted

Re: How to search the count of each value in a multivalue field, and display the corresponding total in a table?

Legend

Try this

index="random" host="xxxxxx" Fruits=*"Apple" |stats count by Fruits
0 Karma
Highlighted

Re: How to search the count of each value in a multivalue field, and display the corresponding total in a table?

SplunkTrust
SplunkTrust

Assuming your field values is in this format,

...other raw data... Fruits="-Apples = 10" ...other raw data...
...other raw data... Fruits="-Apples = 20" ...other raw data...
...other raw data... Fruits="-Oranges= 50" ...other raw data...

Try like this

index="random" host="xxxxxx" | rex field=Fruits "-(?<Fruits>\w+)\s*=\s*(?<Count>\d+)" | stats sum(Count) as Total by Fruits

If not, please share some sample raw data.

0 Karma
Highlighted

Re: How to search the count of each value in a multivalue field, and display the corresponding total in a table?

SplunkTrust
SplunkTrust

You need to use mvexpand to break out the multivalue Fruits field into one record per value, then rex to extract the count, then sum up whatever you are interested in.

If you only want the total count for Apples, then the code looks like this -

index=myindex host=myhost Fruits=*Apple*
| mvexpand Fruits
| search Fruits=*Apple*
| rex field=Fruits "^Apple = (?<FruitCount>[0-9]+)$"
| stats count as RecordCount, sum(FruitCount) as AppleCount

If you want a table of the total values for all fruits, then the code looks like this -

index=myindex host=myhost Fruits=*
| mvexpand Fruits
| rex field=Fruits "^(?<FruitName>[A-Za-z]+) = (?<FruitCount>[0-9]+)$"
| stats count as RecordCount, sum(FruitCount) as FruitCount by FruitName
| table FruitName RecourdCount FruitCount

In general, I'd expect to be adding the min/max date range of the records retrieved, and so on, but that's the basic method.

0 Karma