Splunk Search

Compute last two columns with dynamic table

michaelrosello
Path Finder

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
Tags (3)
0 Karma
1 Solution

nryabykh
Path Finder

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.

View solution in original post

nryabykh
Path Finder

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.

mayurr98
Super Champion

You can try something like this

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

Let me know if this helps!

0 Karma

michaelrosello
Path Finder

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

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