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
Legend

@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
---
What goes around comes around. If it helps, hit it with Karma 🙂

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
Legend

@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
---
What goes around comes around. If it helps, hit it with Karma 🙂

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
Motivator

KailA is an expert on time differences. :yayfox:

Get Updates on the Splunk Community!

Splunk Enterprise Security 8.x: The Essential Upgrade for Threat Detection, ...

 Prepare to elevate your security operations with the powerful upgrade to Splunk Enterprise Security 8.x! This ...

Get Early Access to AI Playbook Authoring: Apply for the Alpha Private Preview ...

Passionate about security automation? Apply now to our AI Playbook Authoring Alpha private preview ...

Reduce and Transform Your Firewall Data with Splunk Data Management

Managing high-volume firewall data has always been a challenge. Noisy events and verbose traffic logs often ...