Archive
Highlighted

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
Highlighted

Re: How to associate dbxquery results with search results?

Splunk Employee
Splunk Employee

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

Highlighted

Re: How to associate dbxquery results with search results?

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
Highlighted

Re: How to associate dbxquery results with search results?

Esteemed Legend

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

0 Karma