Splunk Search

Splunk DB connect multiple Db join

anshu2812
Explorer

Hi,

Can Splunk DB connect (dbquery command) be used to join multiple databases? It tends to table 1 particular database as a paramter so I am not sure if multiple database join queries do actually work? Is it something planned in the next release of 2.0? When is 2.0 suppoed to be released?

Regards
Anshu

Tags (2)

Cuyose
Builder

Unless I am missing something,

I do this all the time. It's very easy if your DB's are on the same server. Just simply append the db name to the table in your FROM condition within the SQL statement. As long as you have the db connection specified to the server in splunks external db's, and your user has access.

|dbquery {externalDbconnection} "Select a.field1, b.field2 FROM db1.table1 a JOIN db2.table2 b ON b.field2=a.field1"

No need to do a join outside of the SQL

wongan3
Engager

If i would like to join 2 databases, and may some subtraction in between, how can do that?

Like this SQL:
SELECT A.msga, (A.REQUEST_DT_GMT + 8/24) - B.SUBMISSION_TIME) AS "TIME_DIFF"
FROM tablea A,
tableb B
WHERE A.msga = B.msgb

ziegfried
Influencer

The dbquery command doesn't directly allow you to join data from multiple databases, but you can use the Splunk search language to combine results from multiple queries that can come from different databases:

| dbquery dbA "SELECT * FROM some_table" | join some_id [ dbquery dbB "SELECT * FROM another_table" ]

In the example above, the results of both queries would be joined by the field/column "some_id".

Another example would be to use the append command to form a union of 2 query results:

| dbquery dbA "SELECT * FROM some_table" | append [ dbquery dbB "SELECT * FROM another_table" ]

gkanapathy
Splunk Employee
Splunk Employee

Seems to me it would be more efficient and easier to do this join in the DB itself using a query containing the join, rather than sending both tables to Splunk and having it do it (using the join search command). But if you're doing this from different databases (that don't otherwise talk to each other), then there's really not much that the DB Connect app can do to bring the data together, other than simply bring both sets into Splunk and allow you to run stats or one of the other commands. (I will warn you that join is usually one of the worst ways to get results in Splunk. Usually, stats or other command are able to get the results much more efficiently.)

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...