Splunk Search

Time difference between events and NULL when second event does not appear in logs

bburns2122
Explorer

I'm trying to figure out how to get the time difference between two events that use the same UUID. However, the second event doesn't always happen. How can I add to my query or alter it to show the time difference between event1 and event2 AND just make event2 NULL if it does not exist with the UUID. Here is what I have started that shows the UUID, time_start of event1, time_finish of event2 and difference.

I think I might need to use the transaction command instead of stats but would like to avoid anything too resource intensive.

index=* sourcetype=* eventCode="event1" serviceCallUUID=*
| stats latest(_time) as time_start by serviceCallUUID
| join serviceCallUUID
[ search index=* sourcetype=* eventCode="event2" serviceCallUUID=*
| stats latest(_time) as time_finish by serviceCallUUID ]
| eval difference=time_finish-time_start
| eval difference=strftime(difference,"%M:%S")
| eval time_finish=strftime(time_finish,"%m-%d-%Y %H:%M:%S.%f")
| eval time_start=strftime(time_start,"%m-%d-%Y %H:%M:%S.%f")

 

Thanks!

Labels (4)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

I'm not sure if you're not overcomplicating a bit.

Firstly - do you really have many event1's for start so you need to look for latest()? (same goes for event2). Secondly, join doesn't seem to be the proper tool here (in splunk it rarely is). Maybe you just need to use "| transaction". Then you have transaction duration calculated automatically.

Oh, and it's usually better to fieldformat times, not eval them. The difference is that if you do

 | fieldformat whatever=strftime(whatever,"yourformat")

instead of eval-ing it, the field itself stays as a unix timestamp (a number) and you can easily compare it to other timestsmps, recalculate, add offsets and so on whereas if you cast it permanently to a string (by eval) you need to re-parse it again if you want to do any of those things.

And for duration you should rather use tostring(duration,"duration") instead of fooling around with strftime.

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...