topic Re: join tables in Splunk Search

join tables

jotaforense — Mon, 20 Jul 2020 14:33:14 GMT

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?

Re: join tables

gcusello — Mon, 20 Jul 2020 14:44:05 GMT

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

Re: join tables

jotaforense — Mon, 20 Jul 2020 14:50:32 GMT

I'm using the first case!

Re: join tables

jotaforense — Mon, 20 Jul 2020 16:48:27 GMT

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

Re: join tables

gcusello — Tue, 21 Jul 2020 06:43:26 GMT

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