I have the following events
**2019-09-20 01:39:25 INFO Listener processing event with message metal:AUD:ADJ
2019-09-19 23:58:27 INFO Listener processing event with message metal:USD:ADJ
2019-09-19 23:58:20 INFO Listener processing event with message metal:USD:ADJ
2019-09-19 23:19:30 INFO Listener processing event with message metal:AUD:ADJ
2019-09-21 04:15:55 INFO Listener processing event with message metal:AUD:ADJ
2019-09-20 21:12:11 INFO Listener processing event with message metal:USD:ADJ
2019-09-21 04:15:45 INFO Listener processing event with message metal:AUD:ADJ
2019-09-20 21:12:17 INFO Listener processing event with message metal:USD:ADJ**
These events are exactly same in pattern, and they only differ in timestamps (The events come in pairs, sometimes there are many pairs with the same key). The timestamps are startTime and endTime. I am trying to summarise these events, by grouping the pair into one row, and calculating the duration.
So the output will look like this.
**product | currency | type | startTime | endTime | duration
metal | AUD | ADJ | 2019-09-20 01:39:25 | 2019-09-19 23:19:30 | 5
|2019-09-21 04:15:45 | 2019-09-21 04:15:55 | 10
metal | USD | ADJ | 2019-09-19 23:58:20 | 2019-09-19 23:58:27 | 7
| 2019-09-20 21:12:11 | 2019-09-20 21:12:17 | 6**
I have began my search as follows:
source="*.log"
| rex field=_raw "message (?<product>.*?):(?<Currency>.*?):(?<type>.*)"
| table Config Intent Currency RunType AccountingDate _time
I have tried both the transaction and stats function, yet in vain. Maybe I am not using them correctly.
Thanks to David, I made some progress by using the first/last(earliest/latest) function. However they only capture the first and last event. Is there a way to group them by the same key, and aggregate the values like my output as above.
Is there anyone who can give me any advice on this issue? Any help will be highly appreciated.
... View more