Splunk Search

Integrating dbxquery in a search

balcv
Contributor

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

Tags (2)
0 Karma
1 Solution

balcv
Contributor

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

View solution in original post

0 Karma

balcv
Contributor

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
0 Karma

MousumiChowdhur
Contributor

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]

0 Karma

balcv
Contributor

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

0 Karma

MousumiChowdhur
Contributor

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"]

0 Karma

balcv
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...