Knowledge Management

How to search events according range in a lookup?

mxh7777
Path Finder

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

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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]

 

 

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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]

mxh7777
Path Finder

Hi @ITWhisperer 
Thanks for answering but unfortunately it doesn't work

No results returned

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Sorry, I missed the "not" in your requirement - can you confirm that there are no overlapping time periods (as in your example)?

mxh7777
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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]

 

 

mxh7777
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Sorry, copy/paste error! 😁

0 Karma

mxh7777
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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]

mxh7777
Path Finder

It's perfect !!!

Thanks a lot

0 Karma

mxh7777
Path Finder

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.

mxh7777_0-1658320639547.png

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 :

Error in 'fillnull' command: Invalid argument: 'earliest=1647558000.000'

I hope it's clear
 
Thanks in advance
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...