Splunk Search

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

wtaylor149
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

woodcock
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

woodcock
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

wtaylor149
Explorer

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

0 Karma

woodcock
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

kearaspoor
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

jordanperks
Path Finder

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

0 Karma

wtaylor149
Explorer

I tried it this way but it did not work.

0 Karma

koshyk
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

wtaylor149
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

koshyk
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

wtaylor149
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

wtaylor149
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

hanamesh
New Member

Works fine to me. Thanks!

0 Karma

koshyk
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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!