Splunk Search

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

tchintam
Path Finder

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?

0 Karma
1 Solution

tiagofbmm
Influencer

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]

View solution in original post

p_gurav
Champion

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

tiagofbmm
Influencer

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
Path Finder

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

0 Karma

tchintam
Path Finder

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

0 Karma

tiagofbmm
Influencer

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
Path Finder

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

0 Karma

tiagofbmm
Influencer

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
Explorer

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

0 Karma

tiagofbmm
Influencer

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

tchintam
Path Finder

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

0 Karma

timmag
Explorer

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

0 Karma

tiagofbmm
Influencer

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
Explorer

Instead of all this, can I use a savedsearch?

0 Karma

tiagofbmm
Influencer

Yes sure why not?

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

tchintam
Path Finder

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?

0 Karma

tiagofbmm
Influencer

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

0 Karma

tchintam
Path Finder

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.

0 Karma

tiagofbmm
Influencer

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

tchintam
Path Finder

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

0 Karma

tiagofbmm
Influencer

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

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...