Hello,
I got a lookup file with differents range of time (start, end) looks like this
Debut, Fin
2020-12-05 12:00:00, 2020-12-05 18:00:00
2021-01-24 08:00:00, 2021-01-24 18:00:00
2021-02:10 19:00:00, 2021-02-10 21:00:00
2021-02-02 19:00:00, 2021-02-02 21:00:00
I'd like to match events which are not included in the differents range of the lookup
I tried this but it didn't work
index="my_index"
[inputlookup my_lookup.csv
| eval start=strptime(Debut,"%Y-%m-%d %H:%M:%S")
| eval end=strptime(Fin,"%Y-%m-%d %H:%M:%S")
| table start end]
| search _time < start AND _time > end
Any idea ?
Thanks for help
It gets a bit complicated - essentially you need to create time periods for the gaps, starting from the end of the previous period and finishing before the start of the next period. Try something like this.
index="my_index"
[|inputlookup my_lookup.csv
| eval start=strptime(Debut,"%Y-%m-%d %H:%M:%S")
| eval end=strptime(Fin,"%Y-%m-%d %H:%M:%S")
``` Sort by time ```
| sort 0 end
``` Get time frame for search ```
| addinfo
``` Exclude time ranges not within search ```
| where end > info_min_time AND start < info_max_time
``` Create additional event to handle final time period ```
| append
[| makeresults
| fields - _time
| addinfo
| rename info_max_time as start
| fields start]
``` Copy end of previous time period as earliest for gap ```
| streamstats values(end) as earliest window=1 current=f
``` Set end of gap to start of time period ```
| eval latest=if(isnull(end), if(earliest > start, null(), start), start)
``` Set start of gap for first period to beginning of search period ```
| eval earliest=if(isnull(earliest), if(start < info_min_time, null(), info_min_time), earliest)
``` Only keep gaps which are within search period ```
| where isnotnull(earliest) AND isnotnull(latest)
| fields earliest latest]
Try something like this
index="my_index"
[|inputlookup my_lookup.csv
| eval earliest=strptime(Debut,"%Y-%m-%d %H:%M:%S")
| eval latest=strptime(Fin,"%Y-%m-%d %H:%M:%S")
| fields earliest latest]
Hi @ITWhisperer
Thanks for answering but unfortunately it doesn't work
No results returned
Sorry, I missed the "not" in your requirement - can you confirm that there are no overlapping time periods (as in your example)?
No, there are not overlapping time periods
And you're right, I want all events that are not in the differents range of my lookup.
So, how can I adapt my request ?
Thanks
It gets a bit complicated - essentially you need to create time periods for the gaps, starting from the end of the previous period and finishing before the start of the next period. Try something like this.
index="my_index"
[|inputlookup my_lookup.csv
| eval start=strptime(Debut,"%Y-%m-%d %H:%M:%S")
| eval end=strptime(Fin,"%Y-%m-%d %H:%M:%S")
``` Sort by time ```
| sort 0 end
``` Get time frame for search ```
| addinfo
``` Exclude time ranges not within search ```
| where end > info_min_time AND start < info_max_time
``` Create additional event to handle final time period ```
| append
[| makeresults
| fields - _time
| addinfo
| rename info_max_time as start
| fields start]
``` Copy end of previous time period as earliest for gap ```
| streamstats values(end) as earliest window=1 current=f
``` Set end of gap to start of time period ```
| eval latest=if(isnull(end), if(earliest > start, null(), start), start)
``` Set start of gap for first period to beginning of search period ```
| eval earliest=if(isnull(earliest), if(start < info_min_time, null(), info_min_time), earliest)
``` Only keep gaps which are within search period ```
| where isnotnull(earliest) AND isnotnull(latest)
| fields earliest latest]
Oups sorry,
I've just changed this
| eval start=strptime(Debut,"%Y-%m-%d %H:%M:%S")
| eval end=strptime(Fin,"%Y-%m-%d %H:%M:%S")
and it works perfectly
What a job !!
Thanks a lot
Sorry, copy/paste error! 😁
Hi @ITWhisperer
Thanks again for the solution.
However, I've seen a little issue when the period selected is not overlapped with a range of my lookup.
Ex.
Considering my lookup :
2020-12-05 12:00:00, 2020-12-05 18:00:00
2021-01-24 08:00:00, 2021-01-24 18:00:00
2021-02-10 19:00:00, 2021-02-10 21:00:00
If I make a search on 2021-01-29 (for example), no results returned.
Any idea ?
Thanks
Try this (if no events, add earliest and latest from addinfo)
index="my_index"
[|inputlookup my_lookup.csv
| eval start=strptime(Debut,"%Y-%m-%d %H:%M:%S")
| eval end=strptime(Fin,"%Y-%m-%d %H:%M:%S")
``` Sort by time ```
| sort 0 end
``` Get time frame for search ```
| addinfo
``` Exclude time ranges not within search ```
| where end > info_min_time AND start < info_max_time
``` Create additional event to handle final time period ```
| append
[| makeresults
| fields - _time
| addinfo
| rename info_max_time as start
| fields start]
``` Copy end of previous time period as earliest for gap ```
| streamstats values(end) as earliest window=1 current=f
``` Set end of gap to start of time period ```
| eval latest=if(isnull(end), if(earliest > start, null(), start), start)
``` Set start of gap for first period to beginning of search period ```
| eval earliest=if(isnull(earliest), if(start < info_min_time, null(), info_min_time), earliest)
``` Only keep gaps which are within search period ```
| where isnotnull(earliest) AND isnotnull(latest)
| appendpipe
[ stats count
| where count=0
| addinfo
| rename info_min_time as earliest
| rename info_max_time as latest]
| fields earliest latest]
It's perfect !!!
Thanks a lot
Hi @ITWhisperer
Sorry for this new request but maybe you could help me.
I'd like to differenciate the empty rows (no log in index) and the rows excluded from the lookup
Example :
Considering the search period 18/03/2022 00:00:00 => 18/03/2022 01:00:00
In my lookup, the range 18/03/2022 00:00:00<=>18/03/2022 00:10:00 is excluded
And there is no log in my source for the period 18/03/2022 00:25:00<=>18/03/2022 01:00:00
I tried this
my_index [|inputlookup my_lookup.csv | eval start=strptime(Debut,"%Y-%m-%d %H:%M:%S") | eval end=strptime(Fin,"%Y-%m-%d %H:%M:%S") | sort 0 end | addinfo | where end > info_min_time AND start < info_max_time | append [| makeresults | fields - _time | addinfo | rename info_max_time as start | fields start] | streamstats values(end) as earliest window=1 current=f | eval latest=if(isnull(end), if(earliest > start, null(), start), start) | eval earliest=if(isnull(earliest), if(start < info_min_time, null(), info_min_time), earliest) | where isnotnull(earliest) AND isnotnull(latest) | appendpipe [ stats count | where count=0 | addinfo | rename info_min_time as earliest | rename info_max_time as latest] | fields earliest latest] | timechart span=5m values(URI) as URI sum(NB) as nb avg(DUR) as DUR | fillnull value=NO_LOG
And here is the result.
Problem : I don't want the 2 first rows to be identified as NO_LOG
I tried to identify the "NO_LOG" rows before the lookup exclusion like this
my_index | timechart span=5m values(URI) as URI sum(NB) as nb avg(DUR) as DUR | fillnull value=NO_LOG [|inputlookup my_lookup.csv | eval start=strptime(Debut,"%Y-%m-%d %H:%M:%S") | eval end=strptime(Fin,"%Y-%m-%d %H:%M:%S") | sort 0 end | addinfo | where end > info_min_time AND start < info_max_time | append [| makeresults | fields - _time | addinfo | rename info_max_time as start | fields start] | streamstats values(end) as earliest window=1 current=f | eval latest=if(isnull(end), if(earliest > start, null(), start), start) | eval earliest=if(isnull(earliest), if(start < info_min_time, null(), info_min_time), earliest) | where isnotnull(earliest) AND isnotnull(latest) | appendpipe [ stats count | where count=0 | addinfo | rename info_min_time as earliest | rename info_max_time as latest] | fields earliest latest]
but I got an error :