topic Re: How to perform a search in an index which filters out results with matching IPs and timestamps in the lookup table in Splunk Search

How to perform a search in an index which filters out results with matching IPs and timestamps in the lookup table

canyin — Thu, 26 Mar 2020 17:05:05 GMT

Hi,

I have a CSV file as lookup table which contains IP address and timestamp as fields. I need to perform a search in an index which filters out results with matching IPs and timestamps in the lookup table.

I can filter out events with matching IPs with the following search string:

index = index  [|inputlookup lookuptable.csv | table src_a | rename src_a as src]

The thing I just can't figure out is how could I match events with _time field and timestamp field in the lookup table. Timestamps in the file follow the same format as _time, for example, 2020-02-24T12:10:10.000+02:00

What should I add to the search string to match timestamps as well?

Thanks in advance!

Re: How to perform a search in an index which filters out results with matching IPs and timestamps in the lookup table

richgalloway — Thu, 26 Mar 2020 17:38:41 GMT

First, _time is in epoch form, not a string. It is converted to string format automatically when displayed.

Second, how likely are you to get an event with a given IP address at the very second specified in the lookup file? If the IP appears at different times it will not be filtered.

If you still want to go down this path, try this query.

index = index  [|inputlookup lookuptable.csv | table src_a | eval _time=strptime(timestamp, "%Y-%m-%dT%H:%M:%S.%3N%:z") | rename src_a as src | fields src, _time | format]

Re: How to perform a search in an index which filters out results with matching IPs and timestamps in the lookup table

manjunathmeti — Thu, 26 Mar 2020 17:45:18 GMT

You cannot format _time field. Instead of using _time to filter use actual timestamp field in the index whose values are same as _time.

index = index  [|inputlookup lookuptable.csv | table src_a, timestamp | rename src_a as src, timestamp as timestamp_field_in_index | format]

Re: How to perform a search in an index which filters out results with matching IPs and timestamps in the lookup table

richgalloway — Wed, 30 Sep 2020 04:43:28 GMT

Good point about format and _time. Your solution should work if the OP's events have two timestamp fields (_time and another).

Re: How to perform a search in an index which filters out results with matching IPs and timestamps in the lookup table

woodcock — Fri, 03 Apr 2020 13:13:45 GMT

Your lookup file probably has an incorrect format for time. You did not tell us the names of your fields so I am going to assume that you have a field called _time in your lookup file. Start with this:

|inputlookup lookuptable.csv | rename _time AS time

If the values for time are numbers, you are good-to-go. If not, you need to convert it like this:

|inputlookup lookuptable.csv
| rename YourTimeFIeldNameHere AS _time
| eval _time = strptime(_time, "%Y-%m-%dT%H:%M:%S.%3n%z")
| outputlookup lookuptable.csv

OK, now you have a valid name for your time field and valid values, too.
Now you can create a lookup definition that will allow you to do a time-based lookup OR filter like this:

index = index AND [|inputlookup lookuptable.csv | table _time src_a
| rename _time AS time
| format
| rex field=search mode=sed "s/time/_time/g" ]