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

[dfraseman]

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=export_start, selected_WO=XXXXXX
Event2: eventtype=export_in_progress, period_WO=XXXXXX

For successful exports, there will be an export_in_progress event that has a period_WO value matching export_start event's selected_WO value. I would like to be able to calculate the time elapsed between the export_start event and the export_in_progress 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.

[richgalloway · Accepted solution]

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

[dfraseman]

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=total_periods
- 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