Splunk Search

How to multiply values within each daily record but group by month?

mkrauss1
Explorer

I'm trying hard to achive the following,
assume i have this data:

DATE=2020-01-01 ITEM1=1 ITEM2=10
DATE=2020-01-02 ITEM1=2 ITEM2=20
DATE=2020-01-03 ITEM1=3 ITEM2=30
....
DATE=2020-01-31 ITEM1=5 ITEM2=40
DATE=2020-02-01 ITEM1=1 ITEM2=10
DATE=2020-02-02 ITEM1=2 ITEM2=20
DATE=2020-02-03 ITEM1=3 ITEM2=20
...
DATE=2020-02-28 ITEM1=4 ITEM2=20

I'd like to multiply ITEM1 with ITEM2 and show it in the field dailytot,
the table query looks then:

DATE=*
| rex field=_raw "DATE=\d+-(?<Month>(.*))-\d+ " 
| rex field=_raw "DATE=(?<Year>(.*))-\d+-\d+ "
| rex field=_raw "DATE=\d+-\d+-(?<Day>(.*)\s{1})ITEM1" 
| stats sum(ITEM1) as ITEM1  sum(ITEM2) as ITEM2 by Month, Year, Day
| eval Daytot = ( ITEM1 * ITEM2)
| addcoltotals ITEM1, ITEM2, Daytot labelfield=Month label=Total

The output looks like:

Month        Year   Day ITEM1       ITEM2       Daytot
01          2020    01  1           10          10
01          2020    02  2           20          40
01          2020    03  3           30          90
01          2020    31  5           40          200
02          2020    01  1           10          10
02          2020    02  2           20          40
02          2020    03  3           20          60
02          2020    28  4           20          80
Total                     21           170         530

All good so fare but i would like to get monthly totals like this:

Month    Year     ITEM1 ITEM2 Daytot
01    2020  11   100    340
02     2020     10   70  190
Total   2020    21   170    530

I was thinking about to append one more search and do one more calculate:

DATE=*
| rex field=_raw "DATE=\d+-(?<Month>(.*))-\d+ " 
| rex field=_raw "DATE=(?<Year>(.*))-\d+-\d+ "
| rex field=_raw "DATE=\d+-\d+-(?<Day>(.*)\s{1})ITEM1" 
| stats sum(ITEM1) as ITEM1  sum(ITEM2) as ITEM2 by Month, Year
 | 
  append [
 | stats sum(ITEM1) as ITEM1  sum(ITEM2) as ITEM2 by Month, Year, Day
 | eval Daytot = ( ITEM1 * ITEM2)   ]
| addcoltotals ITEM1, ITEM2, Daytot labelfield=Month label=Total

But the above try doesn't take me anywhere.

Any idea how to solve this?

0 Karma
1 Solution

adonio
Ultra Champion

like this?

| makeresults count=1
| eval data="DATE=2020-01-01 ITEM1=1 ITEM2=10;;;DATE=2020-01-02 ITEM1=2 ITEM2=20;;;DATE=2020-01-03 ITEM1=3 ITEM2=30;;;DATE=2020-01-31 ITEM1=5 ITEM2=40;;;DATE=2020-02-01 ITEM1=1 ITEM2=10;;;DATE=2020-02-02 ITEM1=2 ITEM2=20;;;DATE=2020-02-03 ITEM1=3 ITEM2=20;;;DATE=2020-02-28 ITEM1=4 ITEM2=20"
| makemv delim=";;;" data 
| mvexpand data
| eval _raw = data
| extract kvdelim="=" pairdelim=" "
| table DATE ITEM1 ITEM2
| rex field=DATE "(?<Year>[^\-]+)\-(?<Month>[^\-]+)\-(?<Day>[^\-]+)"
| eval day_tot = (ITEM1 * ITEM2)
| stats sum(ITEM1) as ITEM1 sum(ITEM2) as ITEM2 sum(day_tot) as Daytot  by Month Year
| addtotals col=true ITEM1 ITEM2 Daytot 
| fields - Total

View solution in original post

0 Karma

adonio
Ultra Champion

like this?

| makeresults count=1
| eval data="DATE=2020-01-01 ITEM1=1 ITEM2=10;;;DATE=2020-01-02 ITEM1=2 ITEM2=20;;;DATE=2020-01-03 ITEM1=3 ITEM2=30;;;DATE=2020-01-31 ITEM1=5 ITEM2=40;;;DATE=2020-02-01 ITEM1=1 ITEM2=10;;;DATE=2020-02-02 ITEM1=2 ITEM2=20;;;DATE=2020-02-03 ITEM1=3 ITEM2=20;;;DATE=2020-02-28 ITEM1=4 ITEM2=20"
| makemv delim=";;;" data 
| mvexpand data
| eval _raw = data
| extract kvdelim="=" pairdelim=" "
| table DATE ITEM1 ITEM2
| rex field=DATE "(?<Year>[^\-]+)\-(?<Month>[^\-]+)\-(?<Day>[^\-]+)"
| eval day_tot = (ITEM1 * ITEM2)
| stats sum(ITEM1) as ITEM1 sum(ITEM2) as ITEM2 sum(day_tot) as Daytot  by Month Year
| addtotals col=true ITEM1 ITEM2 Daytot 
| fields - Total
0 Karma

mkrauss1
Explorer

Thanks for the very interesting solution.
The above records where just a sample and they can be many more.
Would there be a way to feed the data var dynamically and even if, isn't
there a size limit?

0 Karma

mkrauss1
Explorer

Ahh! And many thanks for the improved regex, that looks much better than my one ..

0 Karma

adonio
Ultra Champion

it supposed to scale nicely ...

| makeresults count=1
| eval data="DATE=2020-01-01 ITEM1=1 ITEM2=10;;;DATE=2020-01-02 ITEM1=2 ITEM2=20;;;DATE=2020-01-03 ITEM1=3 ITEM2=30;;;DATE=2020-01-31 ITEM1=5 ITEM2=40;;;DATE=2020-02-01 ITEM1=1 ITEM2=10;;;DATE=2020-02-02 ITEM1=2 ITEM2=20;;;DATE=2020-02-03 ITEM1=3 ITEM2=20;;;DATE=2020-02-28 ITEM1=4 ITEM2=20;;;ATE=2020-01-01 ITEM1=1 ITEM2=10;;;DATE=2020-01-02 ITEM1=2 ITEM2=20;;;DATE=2020-01-03 ITEM1=3 ITEM2=30;;;DATE=2020-03-31 ITEM1=5 ITEM2=40;;;DATE=2022-02-01 ITEM1=1 ITEM2=10;;;DATE=2021-02-02 ITEM1=2 ITEM2=2;;;DATE=2021-02-03 ITEM1=3 ITEM2=20;;;DATE=2021-02-28 ITEM1=4 ITEM2=2"
| makemv delim=";;;" data 
| mvexpand data
| eval _raw = data
| extract kvdelim="=" pairdelim=" "
| table DATE ITEM1 ITEM2
| rex field=DATE "(?<Year>[^\-]+)\-(?<Month>[^\-]+)\-(?<Day>[^\-]+)"
| eval day_tot = (ITEM1 * ITEM2)
| stats sum(ITEM1) as ITEM1 sum(ITEM2) as ITEM2 sum(day_tot) as Daytot  by Month Year
| addtotals col=true ITEM1 ITEM2 Daytot 
| fields - Total
0 Karma

mkrauss1
Explorer

Yes i'm sure that this scales nicely.
The problem is that i don't know what records i have in my splunk database.
Can i feed the data variable out of my search itself instead of specifying each record?

0 Karma

adonio
Ultra Champion

not sure what you mean exactly ...
can you elaborate?
also, if this is a new question, kindly mark this one as answered so others will know it worked for you, you are also welcome to upvote any valuable comments. and then open up a new question

0 Karma

mkrauss1
Explorer

Not sure if this is a new question but its relating to.

The eval data variable is static so each of the record must be entered.

| eval data="DATE=2020-01-01 ITEM1=1 ITEM2=10;;;DATE=2020-01-02 ITEM1=2 ITEM2=20;;;...

But how can i add my records out of my search instead of specifying them in eval data in a manual way? Sorry for the complicated description or maybe i'm missing something fundamental.

0 Karma

adonio
Ultra Champion

just search for your data ...
the entire | makeresults .... just creates fake data
you should put your search string ion the search bar

0 Karma

mkrauss1
Explorer

Excellent!
I was not aware about the makeresults function.
Sorry for all the questions, still a splunk search rookie but promise to get better.

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 ...