topic aggregating field values in Splunk Search

aggregating field values

jedatt01 — Thu, 19 Apr 2012 15:38:18 GMT

I have a specific field that has similar values that I want to group together and obtain an average of another fields value. For example, if the field had the values Bob Sanders, Bob Baker, Sam Winters, and Sam Smith. Each one of these has a numeric value in another field. I would want to group the Bob's and Sam's together and get the average of the values of the numeric field for each.

Like this
Bob, 20
Sam, 36

Anybody have idea's?

Re: aggregating field values

araitz — Thu, 19 Apr 2012 16:20:07 GMT

Assuming that the CSV looks like this:

first,last,age
Bob,Sanders,23
Sam,Johnson,36
Bob,Jackson,39
Sam,Conrad,21

The search would look like:

first=Bob OR first=Sam | stats avg(age) by first

The results would look like this:

first     avg(age)
------------------
Bob       31
Sam       28.5

Re: aggregating field values

jedatt01 — Thu, 19 Apr 2012 17:52:06 GMT

hey araitz, this is jdattilo. It's actually a little more complicated than I first explained. I'm using fictitious data here, but lets say that the data in first and last fields are actually one single field. Can I do a kind of field 'contains' or 'starts with' type of thing? I tried to use substring, which kind of worked but not all my fields entries are the same length so some letters got cut off.

Re: aggregating field values

araitz — Thu, 19 Apr 2012 17:56:57 GMT

The best approach would be to use a field extraction on the field. Conf files are best (using SOURCE_KEY in transforms.conf), but you can also try it in the search language. Let's assume that there is a name field that looks like "Bob Johnson":

`name="Bob *" OR name="Sam *" | rex field=name "(?<first_name>\S+)" | stat avg(age) by first_name`

Re: aggregating field values

amstaff — Thu, 19 Apr 2012 22:37:59 GMT

What you have is a multivalue field. To get to the values use the mvindex command.
mvindex(multifield, 2)

First check if a field is multivalued:
mvcount(multifield)

To get to the values In your case:
| eval name=mvindex(yourmultifield,0) | eval surname=mvindex(yourmultifield,1) | ...

You can also split a field to get a multivalued field.
split(foo, ";")

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions