topic Re: Using Lookup tables to exclude timerange from search in Splunk Search

Using Lookup tables to exclude timerange from search

jedatt01 — Thu, 27 Mar 2014 17:29:13 GMT

I am collecting statistics from an application and am trying to find a way to exclude search results from statistics that occur during a predetermined yearly outage window. I've loaded the outage window details in a lookup table and was wondering how I could use the lookup command inline to exclude from my search results. Lookup table fields and example below.

Date Starttime Endtime
1/10/2014 3:00 AM 5:00 AM
1/24/2014 3:00 AM 7:00 AM
....................................

Re: Using Lookup tables to exclude timerange from search

somesoni2 — Thu, 27 Mar 2014 21:26:54 GMT

Try this workaround. I am not sure about great performance. Also, assumption here is that all Maintenance windows are on hour, no fraction of minutes allowed)

your base search | eval MaintW=_time | bucket span=1h MaintW | where NOT [|inputlookup MaintWindows.csv | eval MainStart=strptime(Date." ".Starttime,"%m/%d/%Y %H:%M %p") | eval MainEnd=strptime(Date." ".Endtime,"%m/%d/%Y %H:%M %p")-3600 | eval temp=MainStart."#".MainEnd | table temp | eval temp=split(temp,"#") | mvexpand temp | rename temp as MaintW | eval MaintW=round(MaintW)]

Re: Using Lookup tables to exclude timerange from search

martin_mueller — Thu, 27 Mar 2014 21:36:19 GMT

Assuming your lookup has epoch time fields start and end, you can do this:

base search | search [inputlookup maintenance_windows | eval search = "(_time < "+start+" OR _time > "+end+")" | fields search | mvcombine search | eval search = "(" + mvjoin(search, " ") + ")"] | ...

That builds a search filter looking like this appended to your base search:

... ((_time < start1 OR _time > end1) (_time < start2 OR _time > end2) ...)

Note: This needs to have its own second search command to avoid contradictions with the time range.

Re: Using Lookup tables to exclude timerange from search

jedatt01 — Fri, 28 Mar 2014 11:22:38 GMT

Are you saying if I was to convert all time values in my lookup to epoch using fields start and end this logic should work?

Re: Using Lookup tables to exclude timerange from search

somesoni2 — Mon, 28 Sep 2020 16:15:54 GMT

Or you can add the conversion to epoch time within the subsearch itself.

base search [inputlookup maintenance_windows | eval start=strptime(Date." ".Starttime,"%m/%d/%Y %H:%M %p") | eval end=strptime(Date." ".Endtime,"%m/%d/%Y %H:%M %p")| eval search = "(_time < "+start+" OR _time > "+end+")" | fields search | mvcombine search | eval search = "(" + mvjoin(search, " ") + ")"]

Re: Using Lookup tables to exclude timerange from search

jedatt01 — Fri, 28 Mar 2014 14:14:40 GMT

Looks like it's not happy with the where NOT, Splunk says its not supported. I'm on Splunk 6

Re: Using Lookup tables to exclude timerange from search

jedatt01 — Fri, 28 Mar 2014 14:24:06 GMT

Ok, almost there. That worked for all time, but does not work if i'm using a relative time (ex. Month to date) is there any way around that?

Re: Using Lookup tables to exclude timerange from search

martin_mueller — Fri, 28 Mar 2014 15:25:06 GMT

In my head it should work for any time range already. Could you post the search filter produced by the subsearch for the non-working example? (See the job inspector's debug output at the top)

Re: Using Lookup tables to exclude timerange from search

jedatt01 — Fri, 28 Mar 2014 16:14:39 GMT

Re: Using Lookup tables to exclude timerange from search

martin_mueller — Fri, 28 Mar 2014 16:30:36 GMT

The time filter generated by the subsearch looks okay.

Did you expect the search peer LOUMON09 to fail? If not, investigate that.

Re: Using Lookup tables to exclude timerange from search

jedatt01 — Fri, 28 Mar 2014 16:38:14 GMT

This is a single server splunk deployment. Not sure what I can check

Re: Using Lookup tables to exclude timerange from search

martin_mueller — Fri, 28 Mar 2014 16:50:10 GMT

Ah... that appears to be the core issue then. To narrow down the cause, let's simplify the search: Replace the subsearch with its output, from double opening to double closing parenthesis inclusive. If that search still fails to start, remove three quarters of the OR'd time filters. If that still fails, remove all but one time filter... and so on.

Additionally, check for errors in the _internal index at the time of search failure.

Re: Using Lookup tables to exclude timerange from search

jedatt01 — Fri, 28 Mar 2014 18:41:10 GMT

Martin, I found the root of the problem. My lookup file has dates for the entire year. When I do a relative lookup those dates aren't applicable. But, when using all time they are. Can I make some modifications to the search to account for this?

Re: Using Lookup tables to exclude timerange from search

martin_mueller — Fri, 28 Mar 2014 18:53:26 GMT

I don't think I understand what you're saying.

What do you mean by "relative lookup"? inputlookup shouldn't care about the timerange of the search.

Re: Using Lookup tables to exclude timerange from search

jedatt01 — Fri, 28 Mar 2014 19:43:00 GMT

Let me try to rephrase. My lookup table has dates and times that span from Jan 2014 through Dec 2014. When I search with the all time range results come up properly. When I search a relative time range (Month to date) it says no results are displayed. I removed dates from my lookup table that were outside of my month to date range and ran the search again and it was successful. So, I have concluded that the search will not complete if the lookup table is referencing dates/times that are outside of my search time.

Re: Using Lookup tables to exclude timerange from search

martin_mueller — Fri, 28 Mar 2014 19:57:08 GMT

Ah! *bonks forehead on table*

Of course... give me a second.

Re: Using Lookup tables to exclude timerange from search

martin_mueller — Fri, 28 Mar 2014 20:25:30 GMT

All-right, got it. The trouble is - Splunk is trying to be too smart for its own good 😄

When specifying the times outside the regular time range, it tries to fiddle with the time range resulting in an error like this:

03-28-2014 21:23:52.306 ERROR ProviderQueue - Error while creating result provider: Error in 'litsearch' command: Unable to parse the search: Invalid time bounds in search: start=1395961200 > end=1395860091.

This can be solved by moving the filter outside of the first search command:

old: base search [subsearch] | ...
new: base search | search [subsearch] | ...

Re: Using Lookup tables to exclude timerange from search

jedatt01 — Fri, 28 Mar 2014 21:02:43 GMT

It works! thanks for hanging in there with me

Re: Using Lookup tables to exclude timerange from search

cphair — Tue, 05 Aug 2014 19:26:59 GMT

@martin_mueller, is there any way to make it work by keeping the earliest/latest time bounds in the base search? My base search is way too large unless I can get the multiple earliest/latest commands working in it.

Re: Using Lookup tables to exclude timerange from search

sansay — Mon, 22 Apr 2019 05:14:01 GMT

@martin_mueller Awesome solution! Just what I was looking for. Thank you so much.