Splunk Search

Replace a column with diagonal value in a table

khavildar
Explorer

host time timediff
a 12:00 END
a 11:55 1
a 11:50 1

I want to replace the "END" in timediff with the below value:
timedifference of the first 2 rows - i.e 12:00 - 11:55
and add the timediff of the second row which is 1 . Essentially , its 5 + 1 = 6 which should be the new value of END

I have used delta command to get the 6 value but am struggling to replace the END with the value.

Tags (2)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@khavildar,

Try this,

    "your search to return above data"
    |reverse|streamstats current=f window=1 last(time) as prev_time,last(timediff) as prev_diff|reverse
    |eval timediff=if(timediff=="END",round((strptime(time,"%H:%M")-strptime(prev_time,"%H:%M"))/60,0)+prev_diff,timediff)
    |fields - prev_diff,prev_time

Full search with dummy data

|makeresults|eval host="a",time="12:00,11:55,11:50",timediff="END,1,1"
|makemv delim="," time|makemv delim="," timediff|eval z=mvzip(time,timediff)|table host,z
|mvexpand z|eval s=split(z,",")|eval time=mvindex(s,0),timediff=mvindex(s,1)|table host,time,timediff
|eval _COMMENT="ALL ABOVE IS JUST TO CREATE DUMMY DATA AND NOTHING TO DO WITH ACTUAL SEARCH :-)"
|reverse|streamstats current=f window=1 last(time) as prev_time,last(timediff) as prev_diff|reverse
|eval timediff=if(timediff=="END",round((strptime(time,"%H:%M")-strptime(prev_time,"%H:%M"))/60,0)+prev_diff,timediff)|fields - prev_diff,prev_time
Happy Splunking!

View solution in original post

khavildar
Explorer

Thanks for that Renjith!
I used your streamstats logic with delta and got it working. Below is how I got the query:

'base search' | table _time,host,timediff | sort host | delta _time as td | eval diff=abs(td) | eval end=timediff+diff | table _time host timediff end | eval n=if(isint(end),end, "0") | reverse | streamstats current=f window=1 last(n) as prev_diff |reverse | eval newrpo=if(timediff="END",prev_diff,timediff) | table _time,VMName,newrpo

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@khavildar,

Try this,

    "your search to return above data"
    |reverse|streamstats current=f window=1 last(time) as prev_time,last(timediff) as prev_diff|reverse
    |eval timediff=if(timediff=="END",round((strptime(time,"%H:%M")-strptime(prev_time,"%H:%M"))/60,0)+prev_diff,timediff)
    |fields - prev_diff,prev_time

Full search with dummy data

|makeresults|eval host="a",time="12:00,11:55,11:50",timediff="END,1,1"
|makemv delim="," time|makemv delim="," timediff|eval z=mvzip(time,timediff)|table host,z
|mvexpand z|eval s=split(z,",")|eval time=mvindex(s,0),timediff=mvindex(s,1)|table host,time,timediff
|eval _COMMENT="ALL ABOVE IS JUST TO CREATE DUMMY DATA AND NOTHING TO DO WITH ACTUAL SEARCH :-)"
|reverse|streamstats current=f window=1 last(time) as prev_time,last(timediff) as prev_diff|reverse
|eval timediff=if(timediff=="END",round((strptime(time,"%H:%M")-strptime(prev_time,"%H:%M"))/60,0)+prev_diff,timediff)|fields - prev_diff,prev_time
Happy Splunking!

KailA
Contributor

Hi,

Can you show us you search, I'm sure I can help you with your problem 🙂

Kail

0 Karma

khavildar
Explorer

Hi!
thanks much but the below answer worked for me! I have pasted the base query and how i worked around it.

0 Karma

horsefez
SplunkTrust
SplunkTrust

KailA is an expert on time differences. :yayfox:

Get Updates on the Splunk Community!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...