Splunk Search

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

vn_g
Path Finder

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.

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

@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

 

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

vn_g
Path Finder

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

vn_g
Path Finder

Current Output :

Disconnected TimeReconnected Time
2021-02-02T23:25:15.0002021-02-02T16:46:19.000
2021-02-02T09:29:59.000
2021-02-02T08:54:48.000
2021-02-02T17:58:18.0002021-02-02T16:46:19.000
2021-02-02T09:29:59.000
2021-02-02T08:54:48.000
2021-02-02T17:36:39.0002021-02-02T16:46:19.000
2021-02-02T09:29:59.000
2021-02-02T08:54:48.000
2021-02-02T14:59:04.0002021-02-02T16:46:19.000
2021-02-02T09:29:59.000
2021-02-02T08:54:48.000
2021-02-02T09:20:59.0002021-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 TimeReconnected Time
2021-02-02T23:25:15.000null
2021-02-02T17:58:18.000null
2021-02-02T17:36:39.000null
2021-02-02T14:59:04.0002021-02-02T16:46:19.000
2021-02-02T09:20:59.000
2021-02-02T09:29:59.000
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

0 Karma

vn_g
Path Finder

autoregress worked.

0 Karma

vn_g
Path Finder

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

@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

 

0 Karma

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

Can you explain the above line

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...