Splunk Search

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

stevenahl
New Member
    | 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?

0 Karma

davebrooking
Contributor

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?

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

aweitzman
Motivator

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.

0 Karma

stevenahl
New Member
 | 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...

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

put dollar signs around the values in the second dbquery

0 Karma

aweitzman
Motivator

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)

0 Karma

stevenahl
New Member

Still no joy.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...