topic Re: How to ignore ip_address from events that exist in a lookup table in Splunk Search

How to ignore ip_address from events that exist in a lookup table

wtaylor149 — Mon, 28 Sep 2020 20:26:15 GMT

Splunk Newbie here....

I'm looking to create a search looking for internal hosts reaching out to external DNS servers. I want to exclude our local internal dns servers as well as the root_dns servers. I have created a lookup table (csv file) that has all the root dns servers. How do I tell my search string to not display the root servers?

Example search:
index=asa dest_port=53 action=blocked dest_ip!=10* AND dest_ip!-172* | use lookup table to remove root_dns_srvrs | stats count by src_ip dest_ip

Thanks for the help. BTW, I don't have access to the cli so if I have to update .conf files, there will be more questions. 🙂

Re: How to ignore ip_address from events that exist in a lookup table

koshyk — Mon, 28 Sep 2020 20:27:04 GMT

could you please try...

<your Search> | join type=left dest_ip  [ | inputlookup your_lookup.csv |  fields  dest_ip  | dedup  dest_ip | eval DummyColumn="Table2"| fields dest_ip, DummyColumn]  | search NOT DummyColumn=*

Try doing a "LEFT" join with left column being "your_search" , have atleast two columns from "your_lookup.csv" and on final output compare whichever is NOT null. (The logic is, lookup left -hand data and anything which is not matched on right-hand side will output null values)

OR
another option is:

| search NOT [|inputlookup dns.csv| table destIp | rename destIp as dest_ip]

Just filterint out any specific data

Re: How to ignore ip_address from events that exist in a lookup table

wtaylor149 — Mon, 28 Sep 2020 20:26:26 GMT

Thanks koshyk but it's not working as expected. Seems I am only pulling the root srvs from the lookup table. Below is the search syntax:

my csv file has 3 colums:
dns_name
dest_ip
exists

Re: How to ignore ip_address from events that exist in a lookup table

koshyk — Wed, 01 Jul 2015 14:30:04 GMT

hmm... not sure I understand when you "only pulling root srvs from lookup". Could you please put few sample events and sample csv file.

Re: How to ignore ip_address from events that exist in a lookup table

wtaylor149 — Mon, 28 Sep 2020 20:26:29 GMT

I'll try to explain, sorry about it but my splunk-foo is just not great.

the query i added - the results in my search are only the root servers that i have listed in the lookup table - which are the ones i don't want to see in my results.

the csv file has 3 columns
dns_name dest_ip exists
a.root-servers.net 198.41.0.4 y
b.root-servers.net 192.228.79.201 y

Unfortunately I am not able to provide samples directly from Splunk. But, the only results I am receiving from the query are the root servers listed in the lookup table.

Re: How to ignore ip_address from events that exist in a lookup table

koshyk — Mon, 28 Sep 2020 20:27:07 GMT

I've simulated a sample scenario by indexing few random IPs and putting some of the sample as "root_dns_srvrs.csv" and it works correctly.

index=myindex | table ips |head 10| rename ips as dest_ip | join type=left [ |inputlookup root_dns_srvrs.csv|  fields  dest_ip| dedup  dest_ip | eval DummyColumn="Table2"]| search NOT DummyColumn=*

This gave me IP's that are present in "left -hand" list , but NOT present in the dns.csv

Re: How to ignore ip_address from events that exist in a lookup table

wtaylor149 — Wed, 01 Jul 2015 17:29:15 GMT

Thank you very much koshyk for your help. This did the trick for the most part. I have this column in my Statistics called DummyColumn that is null, but all the other results are spot on. Thanks again.

Re: How to ignore ip_address from events that exist in a lookup table

kearaspoor — Mon, 28 Sep 2020 20:27:12 GMT

Try simply using the lookup table as part of the search string filtering using a NOT statement, instead of the join.

index=asa dest_port=53 action=blocked dest_ip!=10* AND dest_ip!-172* NOT[ inputlookup your_lookup.csv | fields dest_ip]
| stats count by src_ip dest_ip

Re: How to ignore ip_address from events that exist in a lookup table

jordanperks — Wed, 01 Jul 2015 23:25:15 GMT

This is the best way to do it in my opinion.

Re: How to ignore ip_address from events that exist in a lookup table

wtaylor149 — Thu, 02 Jul 2015 00:59:58 GMT

I tried it this way but it did not work.

Re: How to ignore ip_address from events that exist in a lookup table

woodcock — Tue, 07 Jul 2015 14:33:14 GMT

Unless your CSV file has more than 10500 lines, this should work:

index=asa dest_port=53 action=blocked dest_ip!=10* AND dest_ip!-172* NOT [|inputlookup your_lookup.csv | fields dest_ip] | stats count by src_ip dest_ip

Re: How to ignore ip_address from events that exist in a lookup table

wtaylor149 — Tue, 07 Jul 2015 19:21:49 GMT

Sorry but this answer does not exclude the IP's that are in the lookup table.

Re: How to ignore ip_address from events that exist in a lookup table

woodcock — Tue, 07 Jul 2015 19:32:43 GMT

It most certainly should; did you run it? When you do, click the Job Inspector and check out the Normalized Search. Does it look correct?

Re: How to ignore ip_address from events that exist in a lookup table

hanamesh — Tue, 02 Jul 2019 16:42:10 GMT

Works fine to me. Thanks!