Splunk Search
Highlighted

How to calculate time difference between 2 events with a matching value in different field?

Engager

I'm looking to calculate the elapsed time between 2 events of different types that potentially share a common value but in a different field. The format is something like this:

Event1: eventtype=exportstart, selectedWO=XXXXXX
Event2: eventtype=exportinprogress, period_WO=XXXXXX

For successful exports, there will be an exportinprogress event that has a periodWO value matching exportstart event's selectedWO value. I would like to be able to calculate the time elapsed between the exportstart event and the exportinprogress event with the same value in their respective fields.
Note: the events are different enough in format that both WO's couldn't be extracted using the same field

What I have:

index=... eventtype="export_start"
| eval workorder=selected_WO
| append [ search index=... eventtype="export_in_progress" | eval workorder=period_WO ]
| stats count by workorder _time
| sort - _time

This gives me one long list of all workorder numbers that appear in either field and their timestamps sorted descending order. As an export times out after 30 seconds, my thought was to try and match duplicate workorder numbers within a 30 second time period and calculate the elapsed time.

If this method is reasonable, some help on the last matching piece would be much appreciated. If there is a simpler method to accomplish the same thing, that would be even better!

Thanks in advance.

0 Karma
Highlighted

Re: How to calculate time difference between 2 events with a matching value in different field?

SplunkTrust
SplunkTrust

Try this. It should give you a list of work orders and the differences between start and in-progress times. Performance should be better than with append.

index=foo (eventtype=export_start OR eventtype=export_in_progress
| eval WO = coalesce(selected_WO, period_WO)
| stats range(_time) as diff by WO
| sort - diff
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma
Highlighted

Re: How to calculate time difference between 2 events with a matching value in different field?

Engager

Thanks a lot Rich. This was very helpful. I ended up having to add a few checks to make sure a time difference wasn't being calculated for coalesced WO's with the same eventtype (i.e. export was started more than once but never complete), as well as a time range restriction on the final table output. Here is what ended up working for me:

index=... eventtype=export_start OR eventtype=export_in_progress
| where isnull(period) OR period=total_periods
| eval WO = coalesce(selected_WO, period_WO)
| dedup  WO eventtype
| stats range(_time) as diff by WO
| where diff>0 AND diff<=30
| table WO diff
| sort -diff
| rename WO as "Work Order", diff as "Export Time"

| where isnull(period) OR period=totalperiods
- Narrows events to only the start of the export or the last step (start will have a null period, last export will have 6/6 for example - period = 6, total
periods = 6)

| dedup WO eventtype
- removes duplicates for events with the same WO and eventtype

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.