Splunk Search
Highlighted

How to correlate a lookup table with two columns with a query?

New Member

I have a query that generates a lookup table (IPandUsername.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 (srcip) of Table 1. For source IPs that match ones seen in the srcip 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 srcip match in IPand_Username.csv, I would like it to add the Username to the output. Below are my queries.

First query:
sourcetype="AV"
| dedup srcip, UserName
| table src
ip, UserName
| outputlookup createempty=true IPand_Username.csv

Second query:
sourcetype="HTTP"
| search srcip [IPandUsername.csv]
| rename http.hostname as Domain
| rename http.url as URL
| eval Full
URL = Domain.URL
| table httpstatus, srcip, destip, httpreferer, Full_URL

Thanks in advance!

0 Karma
Highlighted

Re: How to correlate a lookup table with two columns with a query?

Champion

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.

View solution in original post

0 Karma
Highlighted

Re: How to correlate a lookup table with two columns with a query?

New Member

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! 🙂

0 Karma