How can i calculate the time difference with 1 key value and 1 col of data?

[Felipe_Barahona]

Hi there Splunkers,

Maybe the title is a little bit weird but the point is, We have an entity who travel between 2 localizations and we can only register the time when he leave our localization, the problem is I want to calculate the amount of time the entity was outside delivering items.

So the data we managed is something like this

index=main vehicle_patent=LXHH63 Date=1/7/2021
| sort + id
| table id, vehicle_patent, Date, Time

ID	vehicle patent	Date	Time
63	LXHH63	1/7/2021	5:24:12
73	LXHH63	1/7/2021	12:07:05
76	LXHH63	1/7/2021	14:43:57
79	LXHH63	1/7/2021	16:49:49
85	LXHH63	1/7/2021	18:56:31
86	LXHH63	1/7/2021	22:07:51

As you can see the ID value changes because of each entity leaving the place, and this is filtered to only show 1 entity a specific day.

And my output table should look something like this,

ID	vehicle patent	Date	Time	Time_Before*	Time_After*	Diff
63	LXHH63	1/7/2021	5:24:12	5:24:12	5:24:12	0:00:00
73	LXHH63	1/7/2021	12:07:05	5:24:12	12:07:05	6:42:53
76	LXHH63	1/7/2021	14:43:57	12:07:05	14:43:57	2:36:52
79	LXHH63	1/7/2021	16:49:49	14:43:57	16:49:49	2:05:52
85	LXHH63	1/7/2021	18:56:31	16:49:49	18:56:31	2:06:42
86	LXHH63	1/7/2021	22:07:51	18:56:31	22:07:51	3:11:20

*: Optional to be shown

Is this even possible or I'm asking a bit to much? I've been trying for a whole week and no result so far.

Thanks before hands.

[richgalloway]

This query gets you the non-optional fields.

| makeresults | eval _raw="ID	vehicle patent	Date	Time
63	LXHH63	1/7/2021	5:24:12
73	LXHH63	1/7/2021	12:07:05
76	LXHH63	1/7/2021	14:43:57
79	LXHH63	1/7/2021	16:49:49
85	LXHH63	1/7/2021	18:56:31
86	LXHH63	1/7/2021	22:07:51" | multikv forceheader=1
```The above just sets up test data```
```Parse the date/time into an integer so we can compute the diff```
| eval epoch=strptime(Date."T".Time,"%m/%d/%YT%H:%M:%S")
```Compute the diff```
| streamstats range(epoch) as Diff
| eval Diff=round(Diff,0)
```Present the Diff field in H:M:S format```
| fieldformat Diff=tostring(Diff,"duration")
| table ID vehicle_patent Date Time Diff

[ITWhisperer]

This also works if window=2 is included on the streamstats

| makeresults | eval _raw="ID	vehicle patent	Date	Time
63	LXHH63	1/7/2021	5:24:12
73	LXHH63	1/7/2021	12:07:05
76	LXHH63	1/7/2021	14:43:57
79	LXHH63	1/7/2021	16:49:49
85	LXHH63	1/7/2021	18:56:31
86	LXHH63	1/7/2021	22:07:51" | multikv forceheader=1
| eval epoch=strptime(Date."T".Time,"%m/%d/%YT%H:%M:%S")
| streamstats window=2 range(epoch) as Diff
| eval Diff=round(Diff,0)
| fieldformat Diff=tostring(Diff,"duration")
| table ID vehicle_patent Date Time Diff

[ITWhisperer]

You could use autoregress the get the value from the previous row.

| autoregress time as time_before p=1

Then you can do you difference calculation between time and time_before

[Felipe_Barahona]

Never heard of that function before, and it will be pretty useful. THANKS A LOT.