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
....................................
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.
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.
@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.
It works! thanks for hanging in there with me
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] | ...
@martin_mueller Awesome solution! Just what I was looking for. Thank you so much.
Ah! *bonks forehead on table*
Of course... give me a second.
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.
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.
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?
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.
This is a single server splunk deployment. Not sure what I can check
The time filter generated by the subsearch looks okay.
Did you expect the search peer LOUMON09
to fail? If not, investigate that.
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)
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?
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, " ") + ")"]
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?
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)]
Looks like it's not happy with the where NOT, Splunk says its not supported. I'm on Splunk 6