topic Splunk DB connect multiple Db join in Splunk Search

Splunk DB connect multiple Db join

anshu2812 — Sun, 17 Feb 2013 20:37:39 GMT

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

Re: Splunk DB connect multiple Db join

gkanapathy — Sun, 17 Feb 2013 23:50:30 GMT

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.)

Re: Splunk DB connect multiple Db join

ziegfried — Mon, 18 Feb 2013 02:40:44 GMT

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

Re: Splunk DB connect multiple Db join

wongan3 — Mon, 28 Sep 2020 13:48:22 GMT

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

Re: Splunk DB connect multiple Db join

Cuyose — Sun, 20 Jul 2014 18:03:27 GMT

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