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

[wtaylor149]

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

[woodcock · Accepted solution]

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

[wtaylor149]

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

[woodcock]

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?

[kearaspoor]

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

[jordanperks]

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

[wtaylor149]

I tried it this way but it did not work.

[koshyk]

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

[wtaylor149]

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.

[koshyk]

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

[wtaylor149]

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.

[wtaylor149]

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:

index=asa dest_port=53 action=blocked dest_ip!=10* AND dest_ip!=172* AND | join type=left dest_ip [ | inputlookup root_dns_srvrs.csv | fields dest_ip | dedup dest_ip | eval dummycolumn="dns_name" | fields dest_ip dummycolumn ] | search NOT dummycolumn

my csv file has 3 colums:
dns_name
dest_ip
exists

[hanamesh]

Works fine to me. Thanks!

[koshyk]

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