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
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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...