topic Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips? in Splunk Search

I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tchintam — Tue, 29 Sep 2020 18:28:05 GMT

I used this query:

index="abc" source="xyz"
| search [inputlookup example]
| eval End=strptime("End_Date_Time","%Y/%m/%d %H:%M:%S") | eval Start=strptime("Start_Date_Time","%Y/%m/%d %H:%M:%S") | where (_time > End) OR (_time < Start)

This isn't returning any events. Any help?

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tiagofbmm — Tue, 29 Sep 2020 18:28:09 GMT

Make sure the End_Date_Time and Start_Date_Time are both strings and the format of the strptime is correct agains this documentation:

http://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Commontimeformatvariables

index="abc" source="xyz"  [ | inputlookup example | eval latest=strptime(End_Date_Time,"%Y-%m-%d %H:%M:%S") , earliest=strptime(Start_Date_Time,"%Y-%m-%d %H:%M:%S") | return earliest, latest]

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tchintam — Thu, 15 Mar 2018 11:04:42 GMT

That didn't help. Same thing. No events.

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tiagofbmm — Thu, 15 Mar 2018 11:10:15 GMT

Can you show me a line in your example lookup please?

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tchintam — Tue, 29 Sep 2020 18:28:15 GMT

Description End_Date_Time Requested_By Start_Date_Time Maintenance 2018/03/10 12:00:00 Sam 2018/03/10 01:00:00

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tiagofbmm — Thu, 15 Mar 2018 11:16:12 GMT

Btw I had a typo in my answer, try this:

 index="abc" source="xyz"  
[ | inputlookup example
 | eval latest=strptime(End_Date_Time,"%Y-%m-%d %H:%M:%S") , earliest=strptime(Start_Date_Time,"%Y-%m-%d %H:%M:%S") 
| return earliest, latest]

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tiagofbmm — Thu, 15 Mar 2018 11:20:12 GMT

Ok so that was the problem the format of strptime:

  index="abc" source="xyz"  
 [ | inputlookup example
  | eval latest=strptime(End_Date_Time,"%Y/%m/%d %H:%M:%S") , earliest=strptime(Start_Date_Time,"%Y/%m/%d %H:%M:%S") 
 | return earliest, latest]

Try it and let me know

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tchintam — Thu, 15 Mar 2018 11:30:49 GMT

Ah yes. That works. But, what I want is the opposite. I want to exclude the events of the time specified and want the rest of them.

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tiagofbmm — Thu, 15 Mar 2018 11:45:19 GMT

There you go

   index="abc" source="xyz"  
  [ | inputlookup example
   | eval latest=strptime(End_Date_Time,"%Y/%m/%d %H:%M:%S") , earliest=strptime(Start_Date_Time,"%Y/%m/%d %H:%M:%S") 
  | eval maintenance="_time<"+earliest+" OR _time>"+latest 
| return $maintenance]

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

p_gurav — Thu, 15 Mar 2018 11:48:37 GMT

Hi Can you try :

index="abc" source="xyz"  
 [ | inputlookup example
  | eval latest1=strptime(End_Date_Time,"%Y-%m-%d %H:%M:%S") , earliest1=strptime(Start_Date_Time,"%Y-%m-%d %H:%M:%S")] | search _time > latest1 OR _time < earliest1

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tchintam — Thu, 15 Mar 2018 11:50:09 GMT

Thanks a lot! That worked like a charm. Much appreciated.

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tchintam — Thu, 15 Mar 2018 11:56:09 GMT

Any idea how would this work if my lookup has multiple entries?

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tiagofbmm — Thu, 15 Mar 2018 12:08:25 GMT

Yes, just use logic on it, negating many periods of maintenance:

 index="abc" source="xyz"  
   [ | inputlookup example
    | eval latest=strptime(End_Date_Time,"%Y/%m/%d %H:%M:%S") , earliest=strptime(Start_Date_Time,"%Y/%m/%d %H:%M:%S") 
| eval maintenance="NOT (_time>"+earliest+" AND _time<"+latest+")" 
| return 10 $maintenance 
| rex mode=sed field=search "s/OR/AND/g" 
| return $search]

Note the 10 after return is just the number of lines of mantenance periods you may have. Feel free to increase that number to whatever you need.

Let me know if it works

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tchintam — Thu, 15 Mar 2018 12:41:03 GMT

Yes! That works! Awesome. Could you please explain [rex mode=sed field=search "s/OR/AND/g"]? Want to learn.

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tiagofbmm — Thu, 15 Mar 2018 12:43:13 GMT

Yes, the return give you an ORed list of the values you are returning.

But now you need to make a UNION of the maintenance intervals, and that needs to be a logical AND. That is the only thing the rex is doing.

Please UPVOTE the comment if it is useful for you!

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

timmag — Fri, 16 Mar 2018 09:37:38 GMT

Off - topic. What if you have two that index and source in a lookup, how would that work?

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tiagofbmm — Fri, 16 Mar 2018 09:42:21 GMT

Yes, if you want just to filter for specific indexes and sources, just add them to the return command part.

To make sure you are getting what you want, practice with the part of the inputlookup and see the results of something like this:

| inputlookup example
     | eval latest=strptime(End_Date_Time,"%Y/%m/%d %H:%M:%S") , earliest=strptime(Start_Date_Time,"%Y/%m/%d %H:%M:%S") 
 | eval maintenance="NOT (_time>"+earliest+" AND _time<"+latest+")" 
 | return 10 $maintenance, index
 | rex mode=sed field=search "s/OR/AND/g" 
 | return $search

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

timmag — Tue, 29 Sep 2020 18:33:21 GMT

The index and source would be part of a different lookup.
Lookup1:
Index Source
abc xyz
def fgh

Now,
Can I do this?

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

tiagofbmm — Fri, 16 Mar 2018 10:06:40 GMT

You can do that but you are forgetting the return in the return

search [ |inputlookup lookup1 | return 10 index,source] [| inputlookup planned_downtime_ee
| eval latest=strptime(End_Date_Time,"%Y/%m/%d %H:%M:%S") , earliest=strptime(Start_Date_Time,"%Y/%m/%d %H:%M:%S") 
| eval maintenance="NOT (_time>"+earliest+" AND _time<"+latest+")" 
| return 10 $maintenance 
| rex mode=sed field=search "s/OR/AND/g" 
| return $search]

So for any pair of index, source in the lookup1, you are excluding all the maintenance times. This logically only works if the maintenance times are the same for all pairs (index,source).

If not, then you need to have a correspondence between maintenance perior and (index,source) pairs

Re: I'm not able to exclude maintenance time from my events when I use inputlookup. Any tips?

timmag — Mon, 19 Mar 2018 10:14:09 GMT

Instead of all this, can I use a savedsearch?