join tables

[jotaforense]

I would like to obtain the results of two tables.

| dbxquery query = "select * from table1 " connection = "Connection1"

Fields table1:

ID_USER | NR_CARD | DT_CARD

| dbxquery query = "select * from table2 " connection = "Connection1"

Fields table2: 
ID_USER | DS_EMAIL | DS_NAME

the common fields between the two tables is: "ID_USER".
How to join tables 1 and 2 and transfer all fields?

[table1 / 2] ID_USER | [table1] | NR_CARD | [table1] DT_CARD | [table2] DS_EMAIL | [table2] DS_NAME

[gcusello]

Hi @jotaforense,

let me understand: are you using Splunk DB Connect like an Oracle client to run on line queries or you have two inputs, one for each query that store results in one or two indexes?

in the first case, the easiest way is to create a join between the two queries and run only one query that extracts the fields you need.

In the second one, you can correlate two data flows, something like this:

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2)
| stats values(NR_CARD) AS NR_CARD values(DT_CARD) AS DT_CARD values(DS_EMAIL) AS DS_EMAIL values(DS_NAME) AS DS_NAME COUNT BY ID_USER 

Ciao.

Giuseppe

[jotaforense]

I'm using the first case!

[jotaforense]

can you help me how does the join between these two tables work?

[gcusello]

Hi @jotaforense,

sorry but I haven't the knowledge on SQL to do this (many years ago!).

I have only one think to add: this isn't a good approach to Splunk because this solution is very slow, the best approach is to:

• periodically extract data from DB,
• store them in one index,
• use the data from index.

Ciao.

Giuseppe