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