Work out the duration between two fields

[gracemaher]

Hi there.
I basically have a data set with Support Cases in, i would like to find out the duration between the case being created (New) and the case being closed (Resolved).

I have tried using eval/transaction and just cant get it to work.
can anyone help? 🙂
Thanks

[fdi01]

try

...| transaction Field_name startswith="New" endswith="Resolved"|eval duration = duration|...

[woodcock]

The first answer using "stats" (not using "transaction") will be more effective (the max_span, etc. values are infinite) and also MUCH more efficient (quicker to run).

[woodcock]

Let's say the field that you were using in your transaction command is "CorrelationField" then this should work:

... | stats earliest(_time) AS first latest(_time) AS last by CorrelationField | eval duration=last-first