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 :
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?
@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