topic Re: time difference between two rows same field in Splunk Search

time difference between two rows same field

splunksurekha — Fri, 16 Oct 2015 12:13:21 GMT

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

Re: time difference between two rows same field

Yasaswy — Fri, 16 Oct 2015 14:49:40 GMT

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

Re: time difference between two rows same field

woodcock — Fri, 16 Oct 2015 14:56:12 GMT

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

Re: time difference between two rows same field

splunksurekha — Sat, 17 Oct 2015 07:22:20 GMT

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

Thanks Woodcock, but somehow it didnt work for me.

Re: time difference between two rows same field

woodcock — Sat, 17 Oct 2015 14:41:52 GMT

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

Re: time difference between two rows same field

somesoni2 — Sat, 17 Oct 2015 18:57:11 GMT

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

Re: time difference between two rows same field

woodcock — Sat, 17 Oct 2015 22:00:00 GMT

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