So I have a bunch of data and somoene has decided they'd like to know the average turn around time for events.
I can look up the history of event a and extrapolate that it took 15 mins to go from state X to state Y; but how to I write a query that will find the average time between state x and state y for all events in a given time frame?
Okay, I will assume that events look something like this
[timestamp] identifier=A info state=X
[timestamp] identifier=B info state=X
[timestamp] identifier=A info state=Y
[timestamp] identifier=C info state=X
[timestamp] identifier=A info state=Y
[timestamp] identifier=C info state=Y
And additional assumptions must be made: X is always the first state and Y is always the last state. Each identifier is something like a unique transaction id, that can occur only once for each state.
Try this:
yoursearchhere
| stats range(_time) as duration count by identifier
| where count > 1
| stats avg(duration) as AvgEventDuration
The where count > 1
will eliminate partial events. Partial events will occur if a particular identifier is in state X now, but has not yet gone to state Y.