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 destport=53 action=blocked destip!=10* AND destip!-172* | use lookup table to remove rootdnssrvrs | stats count by srcip 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 destport=53 action=blocked destip!=10* AND destip!-172* NOT[ inputlookup yourlookup.csv | fields destip]
| 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 "yoursearch" , have atleast two columns from "yourlookup.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
dnsname destip 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 "rootdnssrvrs.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 destport=53 action=blocked destip!=10* AND destip!=172* AND | join type=left destip [ | inputlookup rootdnssrvrs.csv | fields destip | dedup destip | eval dummycolumn="dnsname" | fields destip dummycolumn ] | search NOT dummycolumn

my csv file has 3 colums:
dnsname
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