Splunk Search

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

HattrickNZ
Motivator

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.

0 Karma
1 Solution

mayurr98
Super Champion

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!

View solution in original post

yannK
Splunk Employee
Splunk Employee

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

0 Karma

HiroshiSatoh
Champion

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]

HattrickNZ
Motivator

Tks that works for me.

0 Karma

mayurr98
Super Champion

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!

HattrickNZ
Motivator

Tks that works for me.

0 Karma
Get Updates on the Splunk Community!

Splunk Classroom Chronicles: Training Tales and Testimonials

Welcome to the "Splunk Classroom Chronicles" series, created to help curious, career-minded learners get ...

Access Tokens Page - New & Improved

Splunk Observability Cloud recently launched an improved design for the access tokens page for better ...

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...