Splunk Search

Using Lookup tables to exclude timerange from search

Builder

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
....................................

Tags (1)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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.

View solution in original post

SplunkTrust
SplunkTrust

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.

View solution in original post

Builder

@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.

0 Karma

Builder

It works! thanks for hanging in there with me

0 Karma

SplunkTrust
SplunkTrust

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] | ...
0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

Ah! *bonks forehead on table*

Of course... give me a second.

Builder

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.

SplunkTrust
SplunkTrust

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.

0 Karma

Builder

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?

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Builder

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

0 Karma

SplunkTrust
SplunkTrust

The time filter generated by the subsearch looks okay.

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

0 Karma

Builder

alt text

0 Karma

SplunkTrust
SplunkTrust

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)

0 Karma

Builder

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?

0 Karma

SplunkTrust
SplunkTrust

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, " ") + ")"]

0 Karma

Builder

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?

0 Karma

SplunkTrust
SplunkTrust

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)]
0 Karma

Builder

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

0 Karma