Including inputlookup value in results

[mpuckettsc]

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?

[gcusello · Accepted solution]

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

[mpuckettsc]

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

[sbbadri]

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

[mpuckettsc]

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.