Splunk DB Connect 1: How to join SQL queries from different servers?

[stevenahl]

    | dbquery Server1 "SELECT value1, value2 FROM db1.table"
    | join type=left value2 [| dbquery Server2 "SELECT value3 FROM db2.table"]
    | table value1, value2, value3

I have two tables, in two databases on two different servers that I need to join. They have a single common value (we'll say value2 here). When running the actual code, "value3" is the same for every row. I'm not sure where I have this wrong, but I don't understand how the splunk join works between two servers like this.

Thoughts?

[davebrooking]

What database and version are you trying to query, is it SQL Server, MySQL or something else. If you were to remove Splunk from the equation and reduce it to a SQL conundrum, Using SQL Server if Server2 is defined as a linked server of Server1, the query could be satisfied with something like

SELECT a.col1, a.col2, b.col3
FROM   Server1.db1.owner.table a
LEFT OUTER JOIN Server2.db2.owner.table b
ON       a.col2= b.col2 

which uses a 4 part table identifier of the form MyServer.MyDatabase.TableOwner.Mytable

Can you do something similar and use the database tools to manage the join and remove the Splunk join command?

[jcoates_splunk]

Agreed -- either do the work in SQL with optimized joins or index the data and do it in SPL, because this hybrid approach is the worst of both worlds.

[aweitzman]

It doesn't appear that you're returning value2 in your query from Server2, so that's pretty much guaranteed not to work. You need to have that field in the table you're joining with.

[stevenahl]

 | dbquery Server1 "SELECT value1, value2 FROM db1.table"
 | join type=left value2 [| dbquery Server2 "SELECT value2, value3 FROM db2.table"]
 | table value1, value2, value3

Still does the same thing...

[jcoates_splunk]

put dollar signs around the values in the second dbquery

[aweitzman]

Does this work?

  | dbquery Server1 "SELECT value1, value2 FROM db1.table"
  | join type=left value2 [search dbquery Server2 "SELECT value2, value3 FROM db2.table"]
  | table value1, value2, value3

(Replaced the pipe with search in the subsearch)

[stevenahl]

Still no joy.