I have a query that generates a lookup table (IP_and_Username.csv) which has two columns in it: src_ip and Username.
I then have a second query that runs, looking for source IPs that match the ones seen in the first column (src_ip) of Table 1. For source IPs that match ones seen in the src_ip column of Table 1, the second query prints out URLs that IP visited.
Here's what I am trying to do:
If the second query finds a src_ip match in IP_and_Username.csv, I would like it to add the Username to the output. Below are my queries.
First query:
sourcetype="AV"
| dedup src_ip, UserName
| table src_ip, UserName
| outputlookup create_empty=true IP_and_Username.csv
Second query:
sourcetype="HTTP"
| search src_ip [IP_and_Username.csv]
| rename http.hostname as Domain
| rename http.url as URL
| eval Full_URL = Domain.URL
| table http_status, src_ip, dest_ip, http_referer, Full_URL
Thanks in advance!
Isn't this exactly what lookup does? In your case, the second search would look like
sourcetype="HTTP"
| lookup IP_and_Username.csv src_ip as src_ip OUTPUT UserName
| rename http.hostname as Domain http.url as URL
| eval Full_URL = Domain.URL
| table http_status, src_ip, dest_ip, http_referer, Full_URL, UserName
The column UserName
will simply be empty for lines where the lookup didn't find a matching src_ip
.
Isn't this exactly what lookup does? In your case, the second search would look like
sourcetype="HTTP"
| lookup IP_and_Username.csv src_ip as src_ip OUTPUT UserName
| rename http.hostname as Domain http.url as URL
| eval Full_URL = Domain.URL
| table http_status, src_ip, dest_ip, http_referer, Full_URL, UserName
The column UserName
will simply be empty for lines where the lookup didn't find a matching src_ip
.
Well, I was looking at a bunch of other things that weren't lookup, so I wasn't getting the right outputs! This is great, thanks! 🙂