topic Join DBConnect SQL query with other datasets in Splunk Search

Join DBConnect SQL query with other datasets

osherma — Mon, 28 Sep 2020 15:23:30 GMT

Hello DB pros,

I'm using DBConnect to query a specific table in an Oracle DB (let's call it "oracle"), which has the column "FLOW_ID". In another dataset (let's call it simply "dataset"), which is NOT imported from a DB, contains the field FlowId.

What I wish to do is to JOIN the results of the DB query and the data from "datasets" where FLOW_ID=FlowId.

In SQL, it would probably look like this:
Select *
From oracle a, dataset b
Where a.FLOW_ID=b.FlowId

I've read about the JOIN function in Splunk documentation as well as in different blogs and previous questions (e.g here, but I couldn't find any information about joining a DB Query with other source types.

I've tried the following queries, but none seem to work:

#1 | dbquery "oracle" "SELECT * FROM sometable" | JOIN FLOW_ID [SEARCH sourcetype=dataset | rename FlowId as "FLOW_ID" | FIELDS FLOW_ID TIMESTAMP]

I suppose that since the fields names are different this doesn't won't work, so I also tried to rename dataset.FlowId into "FLOW_ID"

#2 | dbquery "Oracle" SELECT * FROM sometable" | append [SEARCH sourcetype=dataset | FIELDS ] | WHERE FLOW_ID=FlowId

Any help will be GREATLY appreciated - as you can see, I'm pretty stuck.

Re: Join DBConnect SQL query with other datasets

dstaulcu — Sun, 01 Dec 2013 19:27:43 GMT

is the data in oracle dimensional data or event data? if dimensional data and not rapidly changing, why not output the results of your dbquery to oracle to a lookup table? if the content in oracle is event data, is it feasible for you to index the data by tailing the table as an event source? I imagine it would be very slow to join very large data sets among indexers and remote data sources through dbquery at search time.

Re: Join DBConnect SQL query with other datasets

osherma — Mon, 02 Dec 2013 07:51:46 GMT

Thank you for your answer.
It is event data, and unfortunately, due to its size (hundreds of GBs), cannot be "dumped" into an event source .

I also assume that the queries will take time, but I still wonder how to write queries that work...any ideas?