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

[mkrauss1]

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?

[adonio · Accepted solution]

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

[mkrauss1]

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?

[mkrauss1]

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

[adonio]

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

[mkrauss1]

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?

[adonio]

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

[mkrauss1]

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.

[adonio]

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

[mkrauss1]

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