Splunk Search

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

Explorer

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

Tags (1)
0 Karma
1 Solution

Esteemed Legend

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

View solution in original post

0 Karma

Esteemed Legend

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

View solution in original post

0 Karma

Explorer

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

0 Karma

Esteemed Legend

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?

0 Karma

Communicator

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

Path Finder

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

0 Karma

Explorer

I tried it this way but it did not work.

0 Karma

Super Champion

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

Explorer

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.

0 Karma

Super Champion

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

0 Karma

Explorer

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.

0 Karma

Explorer

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

0 Karma

New Member

Works fine to me. Thanks!

0 Karma

Super Champion

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

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!