Splunk Search

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

jpolson
New Member

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?

0 Karma
1 Solution

vasanthmss
Motivator

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

View solution in original post

vasanthmss
Motivator

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

jpolson
New Member

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!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Check out mvzip.

... | eval Buckets=mvzip(BucketSteps, BucketValue) | ...
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...