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