Splunk Search

How to pull a value from a csv lookup in a subsearch to tack onto a table in the main search results?

Builder

Hello All,

I'm using a lookup table which includes of a bunch of IPs. I use this as a blacklist to search through my logs for those IPs. This works.

What I need to do now is to tack on the IP that actually found the event to the main results as a field. Why? Because I have both a source and destination IP, and it is not readily apparent which one is the blacklisted IP. My current search looks something like this:

index=network_logs sourcetype=snort [|inputlookup ip-blacklist.csv | fields newest_ip | rename newest_ip AS query] | fields srcIp,srcPort,dstIp,dstPort,signature,timestamp,trying_to_figure_out_how_to_get_the_blacklisted_ip_here

How do I tell snort to attach the IP that triggered the event in the first place into the event itself?

1 Solution

Builder

The lookup lines were key. Thanks again. I ended up going with this:

| lookup ip-blacklist.csv newest_ip as srcIp OUTPUT newest_ip AS ip_is_src
| fillnull value="" ip_is_src
| lookup ip-blacklist.csv newest_ip as dstIp OUTPUT newest_ip AS ip_is_dest
| fillnull value="" HVA_IP_is_dest
| eval Blacklisted_IP = ip_is_src + ip_is_dest

Only one IP ever shows up in Blacklisted_IP which works great. Kind of a strange workout but hey, it does the job.

View solution in original post

Builder

The lookup lines were key. Thanks again. I ended up going with this:

| lookup ip-blacklist.csv newest_ip as srcIp OUTPUT newest_ip AS ip_is_src
| fillnull value="" ip_is_src
| lookup ip-blacklist.csv newest_ip as dstIp OUTPUT newest_ip AS ip_is_dest
| fillnull value="" HVA_IP_is_dest
| eval Blacklisted_IP = ip_is_src + ip_is_dest

Only one IP ever shows up in Blacklisted_IP which works great. Kind of a strange workout but hey, it does the job.

View solution in original post

Revered Legend

Try this

  1. Add two new fields to lookup table ip-blacklist.csv , srcIpBlocked ands dstIpBlocked. Both fields should have values as "Y".
  2. Try below search after this

    index=network_logs sourcetype=snort [|inputlookup ip-blacklist.csv | fields newest_ip | rename newest_ip AS query] | fields srcIp,srcPort,dstIp,dstPort,signature,timestamp | lookup ip-blacklist.csv newest_ip as srcIp OUTPUT srcIpBlocked |lookup ip-blacklist.csv newest_ip as dstIp OUTPUT dstIpBlocked | fillnull value="N" srcIpBlocked dstIpBlocked

Now you should have indicator fields which will tell you which ips were blocked.

Builder

Thanks. How do I fix this? Exchanging the "AS query" section in my subsearch with something else? Edit: Actually, it looks like the only spots for IPs in these logs is the src or dest field.

Also, it seems like the problem is related to the order of the lookup OUTPUT commands. If I switch them, I get different results. I tried this and even when I DO get a "Y", the IP doesn't always show up:

| lookup ip-blacklist.csv newest_ip as srcIp OUTPUT srcIpBlocked
| lookup ip-blacklist.csv newest_ip as srcIp OUTPUT newest_ip AS thisistheip
| lookup ip-blacklist.csv newest_ip as dstIp OUTPUT dstIpBlocked
| lookup ip-blacklist.csv newest_ip as dstIp OUTPUT newest_ip AS thisistheip
| fields srcIp,srcPort,dstIp,dstPort,signature,timestamp,srcIpBlocked,dstIpBlocked,thisistheip
0 Karma

Revered Legend

You are searching for ipso in the field _raw, not specifically on secIp and dstIp fields , hence it may be listing where blocked Ips are found in other fields.

0 Karma

Builder

This kind of works, but not fully. For whatever reason, some events return "N" for both the srcIpBlocked and dstIpBlocked. Due to other events in the same table, I can determine that one of the IPs is definitely in the lookup (and why else would it be captured?).

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!