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