topic Re: Splunk DB Connect 1: How to join SQL queries from different servers? in Splunk Search

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

stevenahl — Wed, 27 May 2015 17:13:38 GMT

    | 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?

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

aweitzman — Wed, 27 May 2015 17:21:25 GMT

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.

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

stevenahl — Wed, 27 May 2015 17:42:58 GMT

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

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

aweitzman — Wed, 27 May 2015 18:18:57 GMT

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)

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

stevenahl — Wed, 27 May 2015 18:54:39 GMT

Still no joy.

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

davebrooking — Wed, 27 May 2015 21:36:04 GMT

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?

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

jcoates_splunk — Sat, 30 May 2015 21:41:41 GMT

put dollar signs around the values in the second dbquery

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

jcoates_splunk — Sat, 30 May 2015 21:42:56 GMT

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.