Splunk Search

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

New Member

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!

0 Karma

Esteemed Legend

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

Influencer

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

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

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]
---
If this reply helps you, an upvote would be appreciated.
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!