Splunk Search

join tables

jotaforense
Explorer

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

Labels (1)
Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

jotaforense
Explorer

I'm using the first case!

0 Karma

jotaforense
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

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 ...