Splunk Search

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

splunkfuinator
New Member

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!

0 Karma
1 Solution

jeffland
SplunkTrust
SplunkTrust

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

jeffland
SplunkTrust
SplunkTrust

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.

0 Karma

splunkfuinator
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...