topic Re: Finding the _time difference between two subsearches in Splunk Search

Finding the _time difference between two subsearches

nelsonb — Wed, 23 May 2012 21:51:11 GMT

I'm unable to get this search to output anything except the _time of the first search:

anyway it's a logfile that timestamps when the backgroundworker sub starts a routine followed by another entry where it ENDs. It happens multiple times per source so dedup being used in this way probably isn't the best idea. There are many difference sources being indexed each with a unique name. Is this the way to do this? Thanks in advance.

Re: Finding the _time difference between two subsearches

gkanapathy — Thu, 24 May 2012 00:09:07 GMT

index=collect host=app* "backgroundWorkLoad Completed" 
| stats range(_time) as duration earliest(_time) as time_one latest(_time) as time_two by source

will probably get you the right results efficiently. Otherwise:

index=collect host=app* "backgroundWorkLoad Completed"
| eval time_one=if(match(_raw, "backgroundWorkerLoad\w+Completed(?!\sEND)"),_time,null()
| eval time_two=if(match(_raw, "backgroundWorkerLoad\w+Completed\sEND"),_time,null())
| stats earliest(time_one) as time_one latest(time_two) as time_two by source 
| eval duration=time_two-time_one

should get you the same as what you appear to intend.

Re: Finding the _time difference between two subsearches

nelsonb — Fri, 25 May 2012 18:42:16 GMT

The first search worked great, but the results scared me. Thanks for the help. I was definitely making it more complicated than I should have.

Re: Finding the _time difference between two subsearches

nelsonb — Fri, 25 May 2012 19:36:47 GMT

The one problem with doing the stat by source though is that it's only returning one result by source. Each source has several hundred occurences of these pairs of events happening. Is there some other way to sort the returns? I'm trying a few variations.

Re: Finding the _time difference between two subsearches

gkanapathy — Fri, 25 May 2012 23:42:06 GMT

Okay, then you need to use the transaction command, which automatically calculates duration. Something like:

index=collect host=app* "backgroundWorkLoad Completed" | transaction source startswith=("backgroundWorkLoad Completed NOT END") endswith=("backgroundworkerload Completed END") maxevents=2 | table source duration

might work.

Re: Finding the _time difference between two subsearches

nelsonb — Tue, 29 May 2012 17:53:35 GMT

This returned all the results I was looking for. Thanks!