Evaluate _time against current value and next value in a foreach and mvfilter ?

[dhtran]

Hello,

I need to evaluate my _time against a list of times output from a lookup table and produce a calculated field "nextPeriodTime" which is the next time after _time.

I came quite close to the final desired result by using a combination of eval, forearch and mvfilter.

My search query

index="nxs_mq" 
    | table interstep _time  
    | lookup params_vacations.csv interstep OUTPUT 0900,1000,1100,1200,1300,1400,1500,1600,1700
    | eval nextPeriodTime="" 
    | eval eventTime=strftime(_time,"%H%M") 
    | eval eventDate=strftime(_time,"%Y-%m-%d")
    | foreach *00 [| eval nextPeriodTime = if(isnotnull(mvfilter(match('<<FIELD>>',"Y"))),'<<MATCHSTR>>',"N/A")]

Results

The problem is, for example on the first record, I need the value of nextPeriodTime to be "2020-04-21 12:00:00" instead of "17" (plus, I didn't quite understand why "17" ?) because
_time is after 11:00 and before 12:00, so the next period time from _time will be 12:00.

Is it possible / does it make sense to enrich the mvfilter() part to achieve something like

mvfilter(match('<<FIELD>>',"Y") AND (_time > previous-'<<MATCHSTR>>' AND _time < next-'<<MATCHSTR>>') )

Thanks in advance for your help

[to4kawa · Accepted solution]

 index="nxs_mq" 
| table interstep _time  
| lookup params_vacations.csv interstep OUTPUT 0900,1000,1100,1200,1300,1400,1500,1600,1700
| foreach *00 [eval tmp=mvappend(tmp,'<<FIELD>>')
| eval check=if(mvcount(mvdedup(tmp))=1 AND match(tmp,"Y"),1,0)]
| eval nextPeriodTime = if(check=1, strftime(relative_time(_time,"@h+1h"), "%F %H%M"), "N/A")
| eval eventTime=strftime(_time,"%H%M") 
| eval eventDate=strftime(_time,"%Y-%m-%d")
| fields - tmp check

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Foreach

• matchstr
  • Syntax: matchstr= <string>
  • Description: Replaces <<MATCHSTR>> with part of the field name that matches wildcard(s) in the specifier.

[dhtran]

thank you, although I need to fix some minor details in my lookup file but this works perfectly