hi everyone,forgive me me for Chinese English first, I hope you can read my questions.
|dbxquery connection="ConnectHoneypotDB" maxrows=50 timeout="30" query="select src_host from Whiteip"
src_host
10.10.20.1
172.21.1.12
172.26.0.15
This SQL statement gets all the whitelisted IP from the database.these whitelisted IPs also attack honeypots, because this is allowed.
ok. I can also search for some whitelisted ip attacks events from splunk. like this :
index ="honeypot" sourcetype="honeypot_attack_log white=1 |table _time src_ip attack_type
_time src_ip attack_type
2019/04/15 11:01 10.10.20.1 SYN Scan
2019/04/15 11:01 172.21.1.12 SSH logon attempts
Now, how to associate src_host
with src _ip
, I want to count the number of whitelisted IP attacks 、Last attack time and all attack type by src_host
maybe you will ask, why don't I add |stats count by src_ip
from splunk search results directly ? because some whitelisted IP don't attack honeypots often, they are not active.
I tried to use join
command, but it seems that the search is running very slowly,
|dbxquery connection="ConnectHonepotDB" query="select src_host AS src_ip,whiteip_asset_name from Whiteip"
|join type=left src_ip
[search index="honeypot" sourcetype="honeypot_attack_log" white="1"
|stats last(_time) as lastTime,count,values(attack_type) as attack_type by src_ip]
|sort - count
|eval lastTime=strftime(lastTime."%Y/%m/%d %H:%M:%S")
|fillnull lastTime value="Inactive"
|fillnull count value="0"
|fields src_sip whiteip_asset_name count attack_type lastTime
I am worried that it will affect the performance of the database, whether there is a better search method?
Instead of dbxquery
, why don't you use dblookup
? That will avoid you needing to do an expensive join
@sduff_splunk thank you for your answer. but dblookup
is using search events to lookup the table of database, my requirement is using database information to lookup search result. so I seem to be doing the opposite of dblookup
You do your search, then do the lookup, then do a | where SomeFieldInLookup=*
to trim down the results.