Splunk Search

time difference between two rows same field

splunksurekha
Path Finder

alt text

How to calculate difference between both the times ? One with alertstatus=Problem and other with alertstatus=OK

Tags (2)
1 Solution

Yasaswy
Contributor

Hi,
You can use appendcols.... something like (assuming and alert name of "aname"):
|inputlookup yourlookupfile|search alertname=aname alertdate="*Sep 25*" AND alertstatus="Problem"|eval stime=strptime(alertdate, "%a %b %d %H:%M:%S %Y")|appendcols [|inputlookup yourlookupfile|search alertname=aname alertdate="*Sep 25*" AND alertstatus="OK"|eval etime=strptime(alertdate, "%a %b %d %H:%M:%S %Y")]|eval problemDuration_Min=((etime-stime)/60)|fields alertdate,alertname,host,hostname,problemDuration_Min

you can tweak your search criteria per your requirement...
Check out available time functions and Date/Time format options

View solution in original post

somesoni2
Revered Legend

Try something like this

| inputlookup zbxAlertReport | search alertname="Jboss server.log size exceeded 5GB on dc10schdjob01.syd.sf.priv" alertdate="*Sep 25* | convert mktime(alertdate)  timeformat="%a %b %D %H:%M:%S %Y" | diff attribute=alertdate
0 Karma

splunksurekha
Path Finder

Thank you so much Yasaswy it worked. Thanks a lot.

Thanks Woodcock, but somehow it didnt work for me.

0 Karma

woodcock
Esteemed Legend

I forgot that inputlookup does not create _time so I went back and updated my answer so that it should work.

0 Karma

woodcock
Esteemed Legend

You should try all the answers and whichever one works best, click "Accept" to close out the question.

0 Karma

woodcock
Esteemed Legend

Like this:

| inputlookup zbxAlertReport
| search alertname="Jboss server.log size exceeded 5GB on dc10schdjob01.syd.sf.priv" alertdate="*Sep 25*"
| eval atertEpoch = strftime(alertdate, "%a %b %D %H:%M:%S %Y"
| streamstats current=f last(alertEpoch) AS nextTime
| eval  timeDelta = nextTime - alertEpoch

Yasaswy
Contributor

Hi,
You can use appendcols.... something like (assuming and alert name of "aname"):
|inputlookup yourlookupfile|search alertname=aname alertdate="*Sep 25*" AND alertstatus="Problem"|eval stime=strptime(alertdate, "%a %b %d %H:%M:%S %Y")|appendcols [|inputlookup yourlookupfile|search alertname=aname alertdate="*Sep 25*" AND alertstatus="OK"|eval etime=strptime(alertdate, "%a %b %d %H:%M:%S %Y")]|eval problemDuration_Min=((etime-stime)/60)|fields alertdate,alertname,host,hostname,problemDuration_Min

you can tweak your search criteria per your requirement...
Check out available time functions and Date/Time format options

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...