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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...