Archive

How to associate dbxquery results with search results?

Contributor

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?

Tags (1)
0 Karma

Splunk Employee
Splunk Employee

Instead of dbxquery, why don't you use dblookup? That will avoid you needing to do an expensive join

Contributor

@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

0 Karma

Esteemed Legend

You do your search, then do the lookup, then do a | where SomeFieldInLookup=* to trim down the results.

0 Karma