How can I find the differance between 2 dates on multiple events, grouped by a field, based off of the min and max of another field

fmatera — Wed, 23 Jan 2019 20:18:14 GMT

Here is some data

id          apiid                 answer                     cr_date
493442120   fbnagf42wxpfmmrgrf5b    2019-01-23 19:15:41 2019-01-23 19:15:51
493442118   fbnagf42wxpfmmrgrf5b    2019-01-23 19:15:41 2019-01-23 19:15:51
493441485   fbnagf42wxpfmmrgrf5b    2019-01-23 19:15:18 2019-01-23 19:15:38
493441472   fbnagf42wxpfmmrgrf5b    2019-01-23 19:15:17 2019-01-23 19:15:38
493441471   fbnagf42wxpfmmrgrf5b    2019-01-23 19:15:17 2019-01-23 19:15:38
493440588   fbnagf42wxpfmmrgrf5b    2019-01-23 19:15:17 2019-01-23 19:15:17

493440012   5o3q2siu6lirzfbmprdp    2019-01-23 19:14:49 2019-01-23 19:15:08
493440010   5o3q2siu6lirzfbmprdp    2019-01-23 19:14:49 2019-01-23 19:15:08
493438858   5o3q2siu6lirzfbmprdp    2019-01-23 19:14:42 2019-01-23 19:14:43

493439140   kgjtl44jzdn5wtifg3pi    2019-01-23 19:14:41 2019-01-23 19:14:49
493439137   kgjtl44jzdn5wtifg3pi    2019-01-23 19:14:41 2019-01-23 19:14:49
493439114   kgjtl44jzdn5wtifg3pi    2019-01-23 19:14:41 2019-01-23 19:14:49
493439109   kgjtl44jzdn5wtifg3pi    2019-01-23 19:14:41 2019-01-23 19:14:49
493438775   kgjtl44jzdn5wtifg3pi    2019-01-23 19:14:41 2019-01-23 19:14:41

What I am trying to do is: get the answer time from min(id), the cr_date from max(id) by apiid . Get the difference between each of those by apiid, and display the avg time of all apiid's.

I hope the question makes sense. I have tried a bunch of things, with no success.
Any help would be greatly appreciated. Tks.

Re: How can I find the differance between 2 dates on multiple events, grouped by a field, based off of the min and max of another field

somesoni2 — Wed, 23 Jan 2019 21:34:58 GMT

Give this a try

your base searching giving fields id apiid answer cr_date
| eventstats max(id) as maxid min(id) as minid by apiid
| eval mintime=if(id=minid,answer,null())
| eval maxtime=if(id=maxid,cr_date,null())
| stats values(mintime) as mintime values(maxtime) as maxtime by apiid
| convert mktime(*time) timeformat="%Y-%m-%d %H:%M:%S"
| eval diff=abs(mintime-maxtime)
| stats avg(diff) as AvgTimeInSecs

topic How can I find the differance between 2 dates on multiple events, grouped by a field, based off of the min and max of another field in Splunk Dev

How can I find the differance between 2 dates on multiple events, grouped by a field, based off of the min and max of another field

Re: How can I find the differance between 2 dates on multiple events, grouped by a field, based off of the min and max of another field