How to compare multivalue field with previous and next event value of _time field ?

[vn_g]

Each multi-value field (FiledName: R_time ) which has time value in epoch format should be compared to it previous event time and next event time. The epoch time should be less than previous event and greater than next event.

[ITWhisperer]

Can you provide some example events so we can better understand the requirement?

[vn_g]

Attached is the sample list of how the data looks like.

[bowesmana]

Can you explain from those values, what results you would expect to see

[vn_g]

Current Output :

Disconnected Time	Reconnected Time
2021-02-02T23:25:15.000	2021-02-02T16:46:19.000 2021-02-02T09:29:59.000 2021-02-02T08:54:48.000
2021-02-02T17:58:18.000	2021-02-02T16:46:19.000 2021-02-02T09:29:59.000 2021-02-02T08:54:48.000
2021-02-02T17:36:39.000	2021-02-02T16:46:19.000 2021-02-02T09:29:59.000 2021-02-02T08:54:48.000
2021-02-02T14:59:04.000	2021-02-02T16:46:19.000 2021-02-02T09:29:59.000 2021-02-02T08:54:48.000
2021-02-02T09:20:59.000	2021-02-02T16:46:19.000 2021-02-02T09:29:59.000 2021-02-02T08:54:48.000

So , I am getting the Reconnected Time from a subsearch , which is a multivalue and same for each event.

Each Reconnected Time should be compared with previous event and next event Disconnected time and the output should result in Current Event Reconnected Time  > Current Event Disconnected time and Current Event Reconnected Time < Next Event Disconnected Time.

Excepted output :

Disconnected Time	Reconnected Time
2021-02-02T23:25:15.000	null
2021-02-02T17:58:18.000	null
2021-02-02T17:36:39.000	null
2021-02-02T14:59:04.000	2021-02-02T16:46:19.000
2021-02-02T09:20:59.000	2021-02-02T09:29:59.000

[ITWhisperer]

This will give you the previous and next disconnect times, but it wasn't clear what you wanted to do with them, particularly the previous time since it isn't mentioned as being a requirement for the output

| sort D_time
| autoregress D_time as previous p=1
| sort - D_time
| autoregress D_time as next p=1
| mvexpand R_time

[vn_g]

autoregress worked.

[vn_g]

| table _time R_time
| autoregress _time as previous p=1
| mvexpand R_time
| eval conditioncheck= if(_time<R_time AND R_time<previous,"Yes","No")
| stats values(R_time) as R_time by _time conditioncheck
| eval R_time=if(conditioncheck="No","null",R_time)
| stats values(R_time) as R_time by _time
| sort - _time
| eval R_time=mvfilter(match(R_time, "\d+-\d+-\d+T\d+:\d+:\d+.\d+"))

This helped.

[bowesmana · Accepted solution]

@vn_g 

Here's an example that sets up your table and then performs the steps to get the right time. There are often many ways to skin the cat with Splunk, but this just looks for the smallest time after _time and then uses the gap to recalculate R_time after the aggregation

| makeresults
| eval x=split("2021-02-02T23:25:15.000,2021-02-02T16:46:19.000;2021-02-02T09:29:59.000;2021-02-02T08:54:48.000!2021-02-02T17:58:18.000,2021-02-02T16:46:19.000;2021-02-02T09:29:59.000;2021-02-02T08:54:48.000!2021-02-02T17:36:39.000,2021-02-02T16:46:19.000;2021-02-02T09:29:59.000;2021-02-02T08:54:48.000!2021-02-02T14:59:04.000,2021-02-02T16:46:19.000;2021-02-02T09:29:59.000;2021-02-02T08:54:48.000!2021-02-02T09:20:59.000,2021-02-02T16:46:19.000;2021-02-02T09:29:59.000;2021-02-02T08:54:48.000", "!")
| mvexpand x
| rex field=x "(?<time>[^,]*),(?<R_t>.*)"
| eval _time=strptime(time, "%FT%T.%Q")
| eval R_t=split(R_t,";")
| mvexpand R_t
| eval R_time=strptime(R_t,"%FT%T.%Q")
| table _time, R_time R_t
| stats list(R_time) as R_time by _time
| eval comment="___UP TO HERE JUST SETS UP YOUR EXAMPLE TABLE"
| mvexpand R_time
| eval minReconnectTime=if(_time<R_time, R_time-_time, -1)
| stats min(eval(if(minReconnectTime!=-1,minReconnectTime,null()))) as minReconnectTime by _time
| sort - _time
| eval R_time=strftime(_time + minReconnectTime, "%FT%T.%Q")
| table _time R_time

You want from the line after the 'comment' line

[vn_g]

min(eval(if(minReconnectTime!=-1,minReconnectTime,null())))

Can you explain the above line

[bowesmana]

So, minReconnectTime is calculated here

| eval minReconnectTime=if(_time<R_time, R_time-_time, -1)

as the gap between R_time and _time ONLY if the R_time is after time. If not, minReconnectTime will be -1, i.e. it's handling the cases where the reconnect times were before the disconnect time.

| stats min(eval(if(minReconnectTime!=-1,minReconnectTime,null()))) as minReconnectTime

This is then saying 

- Find the smallest minReconnectTime, where it is not -1, so it will ignore all cases where the reconnect was before the disconnect, as that will have set minReconnectTime to -1.

It's an example of an inline eval statement during the stats calculation. The if statement will then return either minReconnectTime if it's >=0 or a 'null' value if it's -1. The min() aggregation will therefore not do anything in the case of null being returned, so it's effectively only handling the 'reconnect_after_disconnect' cases.

I hope this helps.