Splunk Search

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

Steve_A200
Path Finder

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

Labels (2)
0 Karma
1 Solution

Steve_A200
Path Finder

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.

 

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Tokendelta
2024-06-25 17:20:08.26
2024-06-25 17:23:51.12
363311222.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
```

 

Tags (2)

Steve_A200
Path Finder

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.

 

0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...