How to exclude multiple time ranges in a search?

[hbacbs]

Hi,

I would like to execute a search, where several non-overlapping time ranges are excluded. An exclusion time range is marked by a begin event ("Start exclude messages") and an end event ("End exclude messages") and is typically several minutes long. The number of exclusion ranges within the search time range is not defined in advance: it could be 0, or it could be many.

What I have come up with so far works if there is, at most, one exclusion time range completely within the search time range. However, it does not work if there are several time ranges that should be excluded or if the beginning or end of the search time range lies within an exclusion time range (e.g. no "Start exclude messages" event within search time range):

index=* <some search parameters>
| eval startExcludeTime=[ search index=* "Start exclude messages" | eval time=_time| return $time ] 
| eval endExcludeTime=[ search index=* "End exclude messages" | eval time=_time| return $time ]
| where _time < startExcludeTime OR _time > endExcludeTime

I also tried using a transaction based subsearch which works great to determine the exclusion time ranges but I was again not able to figure out how to exclude multiple time ranges in the main search:

index=* 
| transaction startswith="Start exclude messages" endswith="End exclude messages" 
| eval startExcludeTime=_time | eval endExcludeTime=startExcludeTime+duration

Is there another way how to exclude multiple time ranges from a search? Any help would be highly appreciated.

Thanks,
hbacbs

System: Splunk Enterprise
Version: 7.2.0

[darrenfuller]

so what if you did something like this: wrap the transaction in a subsearch and then format it ...

index=IndexWhereTheDataIs sourcetype=SourcetypeWhereTheDataIs 
   [
    index=IndexWhereTheExcludeMessagesAre sourcetype=SourcetypeWhereTheExcludeMessagesAre "Start exclude messages" OR "End exclude messages"
    | transaction startswith="Start exclude messages" endswith="End exclude messages"
    | eval starttime=_time | eval endtime=_time+duration
    | fields + _time starttime endtime
    | eval exclude="NOT (_time>" + starttime + " AND _time<" + endtime + ")"
    | stats values(exclude) AS excludes
    | nomv excludes
   ]

the results of the subsearch look something like:

NOT (_time>1539615600 AND _time<1539619200) 
NOT (_time>1539622800 AND _time<1539626400) 
NOT (_time>1539630000 AND _time<1539630900)

[hbacbs]

Hi Darren,

thanks for your quick response. The subsearch basically works as expected. Unfortunately it fails if a transaction is not closed because there is a "Start exclude messages" event without matching "End exclude messages" event or vice versa.

Also I could not get the proposed combination of main search and subsearch working. If I directly apply the syntax as I receive an error:

Search Factory: Unknown search command 'index'.
However when I add the search keyword to the subsearch

 index=IndexWhereTheDataIs sourcetype=SourcetypeWhereTheDataIs 
[ search  index= ...

the search result is empty and I could not figure out why since when I execute the searches separately and add the result of the subsearch manually to the main search, it works like a charm.