topic Re: Compute last two columns with dynamic table in Splunk Search

Compute last two columns with dynamic table

michaelrosello — Mon, 05 Feb 2018 02:12:09 GMT

So I have a table that looks like this. What I want is to another column based on the last two column of my table with a formula of latestcolumn(column3) / previouscolumn(column2).

my problem is the number of columns is dynamic which mean I can have a up to 8 columns.

title column1  column2 column3
A         1      2       3
B         4      5       6
C         7      8       9

Here is the search i used to get my initial table

index=main 
| xyseries title column count

Re: Compute last two columns with dynamic table

mayurr98 — Mon, 05 Feb 2018 03:42:29 GMT

You can try something like this

index=main 
 | xyseries title column count | eval column4=round(column3/column2,2)

Let me know if this helps!

Re: Compute last two columns with dynamic table

michaelrosello — Mon, 05 Feb 2018 05:45:35 GMT

as I've said the number of columns is not fixed, so there can be columns 1,2,3,4,5

Re: Compute last two columns with dynamic table

nryabykh — Mon, 05 Feb 2018 08:56:53 GMT

Maybe, this way will suit you.

index=main 
| xyseries title, column, count 
| join title 
    [ search index=main 
    | eventstats values(column) as vals, dc(column) as colcount 
    | eval last=mvindex(vals, colcount-1), prev=mvindex(vals, colcount-2) 
    | where column=last OR column=prev 
    | eval column=if(column=last, "last", "prev") 
    | xyseries title, column, count
    | eval result=prev/last 
    | fields - last, prev]

Though, I believe it's possible to implement it easier.