Splunk Search

time difference between two rows same field

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

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

SplunkTrust
SplunkTrust

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

Path Finder

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

Thanks Woodcock, but somehow it didnt work for me.

0 Karma

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

Esteemed Legend

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

0 Karma

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

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