Splunk Search

How to get the difference between first and last fields of each row

shihabno
New Member

I have a table like below

Month Col1 Col2


Jan 10 20
Feb 30 40
Mar 50 60

and I am looking for output like below

Month Col1 Col2


Jan 10 20
Feb 30 40
Mar 50 60
Diff 40 40 <---- diff of Jan and Mar value

Thanks in advance

Some Splunk Guy

Tags (1)
0 Karma
1 Solution

FrankVl
Ultra Champion

Found a way to do that, by transposing, then calculating the difference and then transposing back (up until the first transpose is just to get to a similar example as in the question):

| makeresults
| eval Month="Jan", Col1="10", Col2="20"
| append
[| makeresults | eval Month="Feb", Col1="30", Col2="40"]
| append
[| makeresults | eval Month="Mar", Col1="50", Col2="60"]
| table Month, Col1, Col2
| transpose
| eval row 4=if(column="Month","diff",'row 3'-'row 1')
| transpose header_field=column
| fields - column

Note: if your actual data is bigger than the example, take into account that by default transpose only transposes 5 rows. You can configure it to transpose more rows by adding the number of rows behind the transpose command (or add 0 to remove the limit). See documentation for details: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose

View solution in original post

0 Karma

FrankVl
Ultra Champion

Found a way to do that, by transposing, then calculating the difference and then transposing back (up until the first transpose is just to get to a similar example as in the question):

| makeresults
| eval Month="Jan", Col1="10", Col2="20"
| append
[| makeresults | eval Month="Feb", Col1="30", Col2="40"]
| append
[| makeresults | eval Month="Mar", Col1="50", Col2="60"]
| table Month, Col1, Col2
| transpose
| eval row 4=if(column="Month","diff",'row 3'-'row 1')
| transpose header_field=column
| fields - column

Note: if your actual data is bigger than the example, take into account that by default transpose only transposes 5 rows. You can configure it to transpose more rows by adding the number of rows behind the transpose command (or add 0 to remove the limit). See documentation for details: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose

0 Karma

shihabno
New Member

Thanks FrankVI. It serves the purpose.
However if i leave the last tranpose and want to remove the default header, how that can be achieved?
So the new output will look like
Month Jan Feb Mar diff
Col1 10 30 50 40
Col2 20 40 50 30

0 Karma

FrankVl
Ultra Champion

That's a bit tricky. If your data always looks exactly like this, you could just do some renames and then filter out the row that contains the header names.

But if the set of months you have in your data varies, that becomes a bit tricky.

PS: now that I think of it: if the number of rows varies, that also means that my row 4='row 3'-'row 1' will not work always, you would somehow need to decide which columns to subtract from each other.

0 Karma

FrankVl
Ultra Champion

Alternative approach that does not use transpose and does not require knowledge of the number of rows:

| makeresults
 | eval Month="Jan", Col1="10", Col2="20"
 | append
 [| makeresults | eval Month="Feb", Col1="30", Col2="40"]
 | append
 [| makeresults | eval Month="Mar", Col1="50", Col2="60"]
 | table Month, Col1, Col2
 | append [|makeresults | eval Month="diff" | fields - _time]
 | eventstats values(Col1) AS Col1values values(Col2) AS Col2values
 | eval Col1=if(Month="diff",tonumber(mvindex(Col1values,mvcount(Col1values)-1))-tonumber(mvindex(Col1values,0)),Col1)
 | eval Col2=if(Month="diff",tonumber(mvindex(Col2values,mvcount(Col2values)-1))-tonumber(mvindex(Col2values,0)),Col2)
 | fields Month Col1 Col2
0 Karma

shihabno
New Member

I used | eval Year_Month = strftime(_time, "%Y-%m") to get months like 2017-12, 2018-01, 2018-02
To get first and last month,
| eventstats first(Year_Month) as first, last(Year_Month) as last

Now if I do
| foreach * [eval Growth='2017-12' - '2018-02'] it works

But variable substitution not working
| foreach * [eval Growth='first' - 'last']

0 Karma

shihabno
New Member

To achieve
Month Jan Feb Mar diff
Col1 10 30 50 40
Col2 20 40 50 30

the problem i see is my column names (Col1, Col2...) will change based on my search. It will be different names and column count will more more than 2

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