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

[tchintam]

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?

[p_gurav]

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

[tiagofbmm · Accepted solution]

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]

[tchintam]

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

[tchintam]

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

[tiagofbmm]

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

[tchintam]

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

[tiagofbmm]

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!

[timmag]

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

[tiagofbmm]

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

[tchintam]

How can I pass that 10 as a value? I mean by counting the number of periods, can I pass it dynamically?

[timmag]

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

Now,
Can I do this?

|inputlookup lookup1 | search[| 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

[tiagofbmm]

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

[timmag]

Instead of all this, can I use a savedsearch?

[tiagofbmm]

Yes sure why not?

Please upvote the comments that were useful after you accepted the answer

[tchintam]

Hello - We're specifying the count of maintenance periods right? Like how many periods are there. We pass it. Instead can we pass it dynamically? Like count the number and pass a variable in the same query? Could you please help?

[tiagofbmm]

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

[tchintam]

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.

[tiagofbmm]

Hi

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]

[tchintam]

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

[tiagofbmm]

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