Splunk Search

Finding the _time difference between two subsearches

nelsonb
Explorer

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

|set diff [ search index="collect" host="app*" | regex _raw="backgroundWorkerLoad\w+Completed(?!\sEND)" | dedup source | rename _time AS time_one ] [ search index="collect" host="app*" | regex _raw="backgroundWorkerLoad\w+Completed\sEND" | dedup source | rename _time AS time_two ] | convert timeformat="%H:%M:%S" ctime(time_one) ctime(time_two) | eval duration=time_two-time_one | table source time_one time_two duration

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.

0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee
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.

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee
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.

nelsonb
Explorer

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

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

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.

0 Karma

nelsonb
Explorer

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.

0 Karma

nelsonb
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...