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. 🙂
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
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
Sorry but this answer does not exclude the IP's that are in the lookup table.
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?
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
This is the best way to do it in my opinion.
I tried it this way but it did not work.
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
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.
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
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.
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
Works fine to me. Thanks!
hmm... not sure I understand when you "only pulling root srvs from lookup". Could you please put few sample events and sample csv file.