Splunk Search

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


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



Labels (4)
0 Karma


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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...