Need help in listing the time gaps in a multi-value field

[Steve_A200]

Hi, 

I need help in extracting the time gaps in a multi-value field represented as Date.

My data output looks like this:

index=myindex
| stats values(_time) as _time values(recs) as recs count by Token
| eval Date= strftime (_time,"%F %H:%M:%S.%2q ")
| where count > 1
| table Token Date

Token        Date
363311    2024-06-25 17:20:08.26
                    2024-06-25 17:23:51.12

231321    2024-06-25 18:10:58.86
                    2024-06-25 18:11:28.12
                    2024-06-25 18:12:19.38
                    2024-06-25 18:13:21.90

827341    2024-06-25 15:17:18.06
                    2024-06-25 15:37:47.93
                    2024-06-25 15:41:03.21

I would like to display the difference in time stamps in a new column called "time_gaps", and it would list the time in seconds between the latest time and the previous time, some Tokens have only 2 time stamps, so there should be only 1 value in the time_gap field, however, other that have 4, should have values representing the time difference between the 1st and 2nd, 2nd and 3rd, 3rd and 4th.

I tried streamstats but it seems I may be doing something wrong. Any clean and effect SPL would be appreciated.

Thanks

[richgalloway]

I think you have the right idea, but streamstats doesn't work with multi-value fields.  Try this untested search

index=myindex Token=*
| streamstats window=2 range(_time) as time_gap by Token
| stats list(_time) as _time list(time_gap) as time_gaps by Token
| eval Date= strftime (_time,"%F %H:%M:%S.%2q")
| where count > 1
| table Token Date time_gaps

[yuanliu]

Obviously you must not use strftime before delta calculation.  With this point out of the way, try

index=myindex
| stats values(_time) as _time values(recs) as recs count by Token
| where count > 1
| fields Token Date
| eval idx = mvrange(0, mvcount(Date))
| eval delta = mvmap(idx, if(idx > 0, tonumber(mvindex(Date, idx)) - tonumber(mvindex(Date, idx - 1)), null()))
| fieldformat Date = strftime(Date, "%F %T.%2N")
| table Token Date delta

The output from your sample data is

Date	Token	delta
2024-06-25 17:20:08.26 2024-06-25 17:23:51.12	363311	222.860000
2024-06-25 18:10:58.86 2024-06-25 18:11:28.12 2024-06-25 18:12:19.38 2024-06-25 18:13:21.90	231321	29.260000 51.260000 62.520000
2024-06-25 15:17:18.06 2024-06-25 15:37:47.93 2024-06-25 15:41:03.21	827341	1229.870000 195.280000

Here is a data emulation for you to play with and compare with real data.

| makeresults format=csv data="Token,        Date
363311,    2024-06-25 17:20:08.26 ::                    2024-06-25 17:23:51.12
231321,    2024-06-25 18:10:58.86 ::                    2024-06-25 18:11:28.12 ::                    2024-06-25 18:12:19.38 ::                    2024-06-25 18:13:21.90
827341,    2024-06-25 15:17:18.06 ::                    2024-06-25 15:37:47.93 ::                    2024-06-25 15:41:03.21"
| eval Date = split(Date, " ::                    ")
| eval Date = strptime(Date, "%F %T.%2N")
``` the above emulates
index=myindex
| stats values(_time) as _time values(recs) as recs count by Token
| where count > 1
| fields Token Date
```

[Steve_A200 · Accepted solution]

Thank you @yuanliu , you solution worked, I had to make minor modifications below, but thank you very much indeed.

Modified the section after "... where count > 1" to below:

| where count > 1
| table Date Token _time
| eval idx = mvrange(0, mvcount(_time))
| eval TimeGaps_Secs = mvmap(idx, if(idx > 0, tonumber(mvindex(_time, idx)) - tonumber(mvindex(_time, idx - 1)), null()))
| fieldformat Date = strftime(_time, "%F %T.%2N")
| table Token Date TimeGaps_Secs

Thank you Again.