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

[jpolson]

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?

[vasanthmss · Accepted solution]

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>.*)"

[jpolson]

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:

my search
| eval splitBucketSteps=split(BucketSteps,",")
| eval splitBucketValue=split(BucketValue,",")
| eval temp=mvzip(splitBucketSteps,splitBucketValue,"##")
| mvexpand temp
| rex field=temp "(?.)##(?.)"
| table extractedBucketSteps extractedBucketValue
| chart sum(extractedBucketValue) by extractedBucketSteps

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

[richgalloway]

Check out mvzip.

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