Splunk DB Connect: How to join multiple tables in multiple databases?

[sharmasplunk]

I am not able to get correct results after joining 2 tables from different databases.

Example: DB1 has table T1 and primary key SR datatype is number, DB2 has T2 and primary_key is BI and data type is number. T2 has SR field with datatype as Varchar2. T2 has field named T3 which I want to show in results in case value of field SR from T1 is matching with value of field SR in T2. please suggest ASAP.

[tkomatsubara_sp]

How about "join" parameter in limits.conf ?
http://docs.splunk.com/Documentation/Splunk/latest/Admin/Limitsconf

[sharmasplunk]

I downvoted this post because he is talking to different direction rather than what the issue is reported

[sharmasplunk]

|dbxquery connection="DB1"
query="SELECT COLUMN1,COLUMN2 FROM TABLE1 where COULMN1 in(123, 124, 234, 235)"
shortnames=1 maxrows=10000|table *|rename COLUMN1 as MAINFIELD
|join type=left MAINFIELD[|dbxquery connection="DB2"
query="SELECT distinct COLUMN3,COLUMN4,COLUMN5 FROM TABLE2"
shortnames=1 maxrows=100]|table COLUMN1,COLUMN2,COLUMN3,COLUMN4

EXPLANATION: COLUMN1 is primary_key in TABLE1 and datatype is number. COLUMN3 contains the same value as COLUMN1 but datatype is "varchar". COLUMN3 has same value as COLUMN1 ALWAYS or it will be null. so i want to do left join. so that if value is not null then it will populate value of COLUMN4 also in final table.

Now i want to join COLUMN4 IN TABLE1 if value of COLUMN1 is equals to value of COLUMN2 field.

my final table looks like
COLUMN1 COLUMN2 COLUMN4

[richgalloway]

Is the data already extracted from the databases into Splunk and you want to know how to query for it or are you asking for the SQL to read from the databases into Splunk?

[sharmasplunk]

i am connecting to databases and getting real time data. i am able to get data from both the databases but join is not working.

Example : DB1 has table T1 and primary key SR datatype is number, DB2 has T2 and primary_key is BI and data type is number. T2 has SR field with datatype as Varchar2. T2 has field named T3 which i want to show in results in case value of field SR from T1 is matching with value of field SR in T2. please suggest ASAP.

[tkomatsubara_sp]

Are you using DB Connector?
If so, there is a potential out of memory issue error. Did you look into the dbx.log under $SPLUNK_HOME/log/splunk/ ?

[sharmasplunk]

there is no issue with memory. i just need how to join the fields from different tables. in other words left join in splunk

Example : DB1 has table T1 and primary key SR datatype is number, DB2 has T2 and primary_key is BI and data type is number. T2 has SR field with datatype as Varchar2. T2 has field named T3 which i want to show in results in case value of field SR from T1 is matching with value of field SR in T2. please suggest ASAP.