Hi, I would like to find out how to calculate the time difference between different events of the same asset ID (group by). My data is structured as such (see below) with no transaction IDs provided:
Asset_ID _time Event_Status
Note:
The desired output would be to compute the time duration between rows as such:
I've previously tried experimenting using the "transaction" and "duration" functions but they don't seem to give the desired result. Any suggestions on how to resolve this would be greatly appreciated. Thanks.
One solution is like this
| makeresults
| eval _raw="Asset_ID _time Event_Status
A001 2021-01-01:00:00:00 A
A001 2021-01-01:00:01:00 B
A001 2021-01-01:00:07:00 A
A002 2021-01-01:00:01:00 B
A002 2021-01-01:00:02:00 C
A002 2021-01-01:00:09:00 A
A002 2021-01-01:00:11:00 D
A003 2021-01-01:00:00:00 B
A003 2021-01-01:00:09:00 D"
| multikv forceheader=1
| eval _time=strptime(time, "%F:%T")
| table Asset_ID _time Event_Status
| sort - _time
| streamstats window=2 global=false range(_time) as duration by Asset_ID
| eval duration=if(duration=0, now()-_time, duration)
From the sort command. Using streamstats to calculate the time between this and previous event and then if duration is 0 (because it's the last event for an Asset_ID) it just calculates current time - event time.
Note that this requires the event order to be in latest_first order as the streamstats calculates the duration for the current event as this event time - earlier event time.
If your data is already in that order you will not need to sort - typically it will be.
The important global=false means that there are separate ranges calculated using the by clause, so you do not need to ensure the events are coming in for the same Asset_ID order.
Note that also if a duration is 0 between any two events, then it assumes it's the last event, so will just calculate now() - _time as the duration. If your events can really have a duration of 0, then you will need to find a solution for that.
Thanks! That was really helpful.
One solution is like this
| makeresults
| eval _raw="Asset_ID _time Event_Status
A001 2021-01-01:00:00:00 A
A001 2021-01-01:00:01:00 B
A001 2021-01-01:00:07:00 A
A002 2021-01-01:00:01:00 B
A002 2021-01-01:00:02:00 C
A002 2021-01-01:00:09:00 A
A002 2021-01-01:00:11:00 D
A003 2021-01-01:00:00:00 B
A003 2021-01-01:00:09:00 D"
| multikv forceheader=1
| eval _time=strptime(time, "%F:%T")
| table Asset_ID _time Event_Status
| sort - _time
| streamstats window=2 global=false range(_time) as duration by Asset_ID
| eval duration=if(duration=0, now()-_time, duration)
From the sort command. Using streamstats to calculate the time between this and previous event and then if duration is 0 (because it's the last event for an Asset_ID) it just calculates current time - event time.
Note that this requires the event order to be in latest_first order as the streamstats calculates the duration for the current event as this event time - earlier event time.
If your data is already in that order you will not need to sort - typically it will be.
The important global=false means that there are separate ranges calculated using the by clause, so you do not need to ensure the events are coming in for the same Asset_ID order.
Note that also if a duration is 0 between any two events, then it assumes it's the last event, so will just calculate now() - _time as the duration. If your events can really have a duration of 0, then you will need to find a solution for that.