topic How do I multiply 3 row values from 1 column and do it in one cell using append pipe. in Splunk Search

How do I multiply 3 row values from 1 column and do it in one cell using append pipe.

HattrickNZ — Tue, 29 Sep 2020 17:50:58 GMT

This is my search:

| makeresults 
 | eval data = "
 1  2017-12 A   155749  131033  84.1;
2   2017-12 B   24869   23627   95;
3   2017-12 C   117618  117185  99.6;
" 
 | makemv delim=";" data 
 | mvexpand data
 | rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
 | fields + date serial type attempts successfullAttempts sr 
 | rename date as _time 
 | search serial=*
 | appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval successfullAttempts="average sr"]  
 | appendpipe [stats sum(sr) as sr | eval sr=round(sr,1) ]
 | appendpipe [ where type="A" | stats sum(sr) as sr ]  
 | appendpipe [ where type="B" | stats sum(sr) as sr ]  
 | appendpipe [ where type="C" | stats sum(sr) as sr ] 
 | appendpipe [ stats sum(sr) as sr | eval sr="want 84*95*99 to go here" ]

This is my result:

_time   serial  type    attempts    successfullAttempts sr
1   2017-12 1   A   155749  131033  84
2   2017-12 2   B   24869   23627   95
3   2017-12 3   C   117618  117185  99
4                   average sr  92.7
5                       370.7
6                       84
7                       95
8                       99
9                       want 84*95*99 to go here

What I want is in row 9 column sr the result fo 84*95*99

    _time   serial  type    attempts    successfullAttempts sr
1   2017-12 1   A   155749  131033  84
2   2017-12 2   B   24869   23627   95
3   2017-12 3   C   117618  117185  99
4                   average sr  92.7
5                       370.7
6                       84
7                       95
8                       99
9                       790020

How can I do this?
How do I multiply 3 row values from 1 column and do it in one cell using append pipe.

Re: How do I multiply 3 row values from 1 column and do it in one cell using append pipe.

mayurr98 — Fri, 26 Jan 2018 03:09:28 GMT

hey you can try something like this

| makeresults 
  | eval data = "
  1    2017-12    A    155749    131033    84.1;
 2    2017-12    B    24869    23627    95;
 3    2017-12    C    117618    117185    99.6;
 " 
  | makemv delim=";" data 
  | mvexpand data
  | rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
  | fields + date serial type attempts successfullAttempts sr 
  | rename date as _time 
  | search serial=*
  | appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval successfullAttempts="average sr"]  
  | appendpipe [stats sum(sr) as sr | eval sr=round(sr,1) ]
  | appendpipe [ where type="A" | stats sum(sr) as sr ]  
  | appendpipe [ where type="B" | stats sum(sr) as sr ]  
  | appendpipe [ where type="C" | stats sum(sr) as sr ]| appendpipe [ stats values(sr) as sr by type | transpose 0 header_field=type  | eval sr=A*B*C | fields sr ]

let me know if this helps!

Re: How do I multiply 3 row values from 1 column and do it in one cell using append pipe.

HiroshiSatoh — Fri, 26 Jan 2018 03:09:41 GMT

Try this!

(your search)
  | appendpipe [ where type="A" OR type="B" OR type="C" |stats values(sr) as sr 
  | eval a=tonumber(mvindex(sr,0))*tonumber(mvindex(sr,1))*tonumber(mvindex(sr,2))|rename a as sr]

Re: How do I multiply 3 row values from 1 column and do it in one cell using append pipe.

yannK — Fri, 26 Jan 2018 03:47:20 GMT

Maybe not the best thing to do, it's hard to do multiplications of rows in splunk.
A possible workaround is to delete all the previous lines, then do a transpose of the 3 lines (to make them columns)
then use eval to multiply them.

      | makeresults 
      | eval data = "
      1    2017-12    A    155749    131033    84.1;
     2    2017-12    B    24869    23627    95;
     3    2017-12    C    117618    117185    99.6;
     " 
      | makemv delim=";" data 
      | mvexpand data
      | rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
      | fields + date serial type attempts successfullAttempts sr 
      | rename date as _time 
      | search serial=*
      | appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval successfullAttempts="average sr"]  
      | appendpipe [stats sum(sr) as sr | eval sr=round(sr,1) ]
      | appendpipe [ where type="A" | stats sum(sr) as srvalue | eval srtitle="srA" ]  
      | appendpipe [ where type="B" | stats sum(sr) as srvalue | eval srtitle="srB"]  
      | appendpipe [ where type="C" | stats sum(sr) as srvalue | eval srtitle="srC"]
      | table srtitle srvalue
      | transpose 20 header_field=srtitle
      | eval sr_calcul=srA*srB*srC

will return something like :
column row 1 row 2 row 3 row 4 row 5 srA srB srC sr_calcul
srvalue 84 95 99 790020

Re: How do I multiply 3 row values from 1 column and do it in one cell using append pipe.

HattrickNZ — Wed, 31 Jan 2018 01:25:22 GMT

Tks that works for me.

Re: How do I multiply 3 row values from 1 column and do it in one cell using append pipe.

HattrickNZ — Wed, 31 Jan 2018 01:25:29 GMT

Tks that works for me.