topic Re: How to bucket sums of a multi-value array with values of another array? in Splunk Search

How to bucket sums of a multi-value array with values of another array?

jpolson — Fri, 14 Jul 2017 16:46:42 GMT

I have some logging being generated that aggregates values for a user in a comma-separated sequence, and has a second field that lists bucketing values in another comma-separated field, like this:

BucketSteps: 1,5,10,20,30,40,60,80,100,120,140,160,180,200,220,240,260,300,9999
BucketValue: 0,0,0,0,35,1382,16887,8412,1093,14,9,4,0,1,2,0,0,0,56

What I need to do is sum all events' bucket values and map them to the bucket step, so in this example 1382 needs to go in the 40 "bucket", 16887 in the 60 "bucket" and so on.

I can separate these value strings just fine with split but I don't see a way of generating the buckets I need outside of a long string of evals mapping each bucket step to a new value, and each bucket value to a new value, then aggregating that way. That makes for a big, unpleasant query.

Is there a more efficient way of working with these arrays that I am missing?

Re: How to bucket sums of a multi-value array with values of another array?

richgalloway — Fri, 14 Jul 2017 17:20:38 GMT

Check out mvzip.

... | eval Buckets=mvzip(BucketSteps, BucketValue) | ...

Re: How to bucket sums of a multi-value array with values of another array?

vasanthmss — Fri, 14 Jul 2017 17:31:59 GMT

try something like this ,

Sample,

|makeresults | eval BucketSteps="1,5,10,20,30,40,60,80,100,120,140,160,180,200,220,240,260,300,9999"  | eval BucketValue="0,0,0,0,35,1382,16887,8412,1093,14,9,4,0,1,2,0,0,0,56" | eval splitBucketSteps=split(BucketSteps,",") | eval splitBucketValue=split(BucketValue,",") | eval temp=mvzip(splitBucketSteps,splitBucketValue,"##") | mvexpand temp | rex field=temp "(?<extractedBucketSteps>.*)##(?<extractedBucketValue>.*)" | table _time, extractedBucketSteps extractedBucketValue

Explanation :

make results,

|makeresults | eval BucketSteps="1,5,10,20,30,40,60,80,100,120,140,160,180,200,220,240,260,300,9999"  | eval BucketValue="0,0,0,0,35,1382,16887,8412,1093,14,9,4,0,1,2,0,0,0,56"

Split

| eval splitBucketSteps=split(BucketSteps,",") | eval splitBucketValue=split(BucketValue,",")

Merget BucketSteps and BucketValue,

| eval temp=mvzip(splitBucketSteps,splitBucketValue,"##") | mvexpand temp | rex field=temp "(?<extractedBucketSteps>.*)##(?<extractedBucketValue>.*)"

Re: How to bucket sums of a multi-value array with values of another array?

jpolson — Fri, 14 Jul 2017 18:04:01 GMT

This is perfect! In my case, I have many sets of BucketSteps and BucketValue to work with so the first eval isn't needed but the rest is exactly what I has hoping for.

In my use case I am carting out a specific timeframe (1 day, for example) so I don't need the time modifier and was able to use this:

which formats to a nice, simple bar chart for my purposes. Thank you very much for the assist!