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.
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