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
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
<string>
<<MATCHSTR>>
with part of the field name that matches wildcard(s) in the specifier. 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
<string>
<<MATCHSTR>>
with part of the field name that matches wildcard(s) in the specifier.thank you, although I need to fix some minor details in my lookup file but this works perfectly