Looking on advice on how to use a inputlookup table value as a raw search string and still be able to include that value in a result table.
I have a csv file with a list of IP addresses which appear to have port scanned us. My goal is to identify other log entries which contain these addresses. For example I want to know if 100.200.100.200 port scanned us and then had a failed login in WinEventLog:Security.
Since I am searching many event types, I don't have consistent IP fields to search across so I'm using a raw string search:
index=* sourcetype!=pan:traffic [|inputlookup portscanners.csv | rename client_ip AS query ]
This gets the results I want, but I'd like to then be able to produce a table with the IP I searched for, the sourcetype and the raw event. But both of these return empty values for the IP field:
index=* sourcetype!=pan:traffic [|inputlookup portscanners.csv | rename client_ip AS query ] | table query,sourcetype,_raw
index=* sourcetype!=pan:traffic [|inputlookup portscanners.csv | rename client_ip AS query ] | table client_ip,sourcetype,_raw
How can I keep this field after searching?
Hi mpuckettsc,
if you have only one IP in your event you could use regex to extract the IP:
index=* sourcetype!=pan:traffic [ | inputlookup portscanners.csv | rename client_ip AS query ]
| rex "(?<IP>\d+\.\d+\.\d+\.\d+)"
| table _time IP sourcetype _raw
Bye.
Giuseppe
Hi mpuckettsc,
if you have only one IP in your event you could use regex to extract the IP:
index=* sourcetype!=pan:traffic [ | inputlookup portscanners.csv | rename client_ip AS query ]
| rex "(?<IP>\d+\.\d+\.\d+\.\d+)"
| table _time IP sourcetype _raw
Bye.
Giuseppe
Thanks! This didn't work totally as events may have multiple IPs, but it got me close enough to get what I wanted. What I did was use the regex to grab all the IPs and then look them up again in the lookup table. So I ended up with:
index=* sourcetype!=pan:traffic [ | inputlookup portscanners.csv | rename client_ip AS query ] | rex max_match=4 "(?<IP>\d+\.\d+\.\d+\.\d+)" | lookup portscanners.csv client_ip as IP OUTPUT client_ip | eval original_ip=mvdedup(client_ip) | table _time,original_ip,sourcetype,_raw
try this
index=* sourcetype!=pan:traffic | lookup portscanners.csv client_ip as extracted_ip_field_from_events OUTPUTNEW field1 field2 | table client_ip sourcetype field1 field2 ....
i hope this helps you
Thanks but that doesn't work and I don't quite follow it enough to tweak it.
Important to note that I'm trying to take the value from the input table and search as a string, I don't have an exact field to try and match it to as I am searching across many sourcetypes. I think that's what you were implying by extracted_ip_field_from_events.