Integrating dbxquery in a search

[balcv]

I have an sql database containing a list of ip addresses and a bunch of other fields that I can query from Splunk using dbxquery

| dbxquery connection="NessusScans" query="select ip,host,critical,high,medium from scans where critical >=1;"

This returns a list of 43 ip addresses and related fields.

Is there anyway that I can then use those IP addresses as the search criteria for a search of indexed data as well.

For example,

index="pan" dest_ip="[ip from dbxquery] | stats count by src_ip 

The result being a table showing some fields the from the database (host,ip,critical,high,medium) then another field being the result of the search.

The end result being something like "myhost | 192.168.1.123 | 2 | 7 | 9 | 35". The first five fields from the dbxquery and the last field from the index search.

Is this possible?
Thanks

[balcv · Accepted solution]

I have figured this one with the help of @MousumiChowdhury. The final solution looked like this:

| dbxquery query="SELECT host,ip as dest_ip,critical,high,medium,low,none,severity,score,lastscan,nessusid,hostid FROM scans where critical > 0 order by severity desc limit 10;" connection="DB Connection"
| join type=outer dest_ip
    [ search index="pan"
    | stats count by dest_ip]
| rename host as Host,dest_ip as IP, critical as Critical,high as High, medium as Medium, low as Low,none as Info, severity as Severity, count as Activity
| table Host,IP,Critical,High,Medium,Low,Info,Severity,Activity
| sort - Critical

This gave me the data I wanted from the SQL database, did a search using the dest_ip and counted events for those IPs with events tfrom the search hen displayed selected records from the database and the Activity value from the search that matched.

Host        IP          Critical     High   Medium  Low Info    Severity    Activity
server1  192.168.61.59  9        1       7     1    107     125
server3  192.168.61.90  6        1      16     4    111     138       220
server19    192.168.61.67    6       0       9     2    100     117
server12    192.168.61.151  5        1      17     4    102     129       214
server8  192.168.61.242 4        9      22     8    127     170

[MousumiChowdhur]

Hello @balcv ,

Could you please try the below query and let me know the output?
| dbxquery query="select ip,host,critical,high,medium from scans where critical >=1;" connection="connection name" | join type=inner ip [search index="pan" | stats count by host,ip,critical,high,medium, src_ip]

[balcv]

Thank you @MousumiChowdhury . I have tried the search you supplied but it returned zero records when I know there should be some results. Thanks.

[MousumiChowdhur]

Can you run the query till here and share if you see your database fields as well as src_ip field?
| dbxquery query="select ip,host,critical,high,medium from scans where critical >=1;" connection="connection name" | join type=inner ip [search index="pan"]

[balcv]

No results were returned at all. If I remove everything after connection="name", the data base results are displayed. SO it's not getting the search data.