All Apps and Add-ons

DB Connect: How union 2 dbxquery from 2 different tables


I have 2 searches:

1) |dbxquery query="select member, gate, port from fo.member connection=fo_member"

2) |dbxquery query="select description from connection=datelog"

How I can union them into one search? I tryed to use |union and |join between each other but it didn't work.

Labels (2)
0 Karma


Don't union or join.  Both of those commands usually have better options available in Splunk so should only be used as a last resort.

A lot of how you'd want to pull them in together depends on

a) Do they have a field in common to group them by?

b) How much data are we talking about, and how large *could* it get?

c) Does either of the RDBMS's behind the scenes have access to the other one?  (You have two connections, but both pulling from "fo" so maybe they're part of the same actual DB?)

If C is true, then by far the best solution is to join these in the RDBMS, which you can do in Splunk.

select date.description member.member, member.gate, member.port from fo.member member INNER JOIN date on date.<date-side-field-to-join-on> = member.<member-side-field-to-join-on>

NOTE that's ONLY the SQL part of things.  You have to work with your DBAs to figure out what that query looks like.  But once you have it, build a ... I do wonder why you have two separate connections to what appears to be possibly just two tables in a single DB? In any case, you would then either build a view in the DB and query that instead, or use the search you came up with as the query inside dbxquery.

If you know they're small and will stay so (A&B above) , the general pattern is

|dbxquery query="select member, gate, port from fo.member connection=fo_member" 
| append [
    |dbxquery query="select description from connection=datelog" ]
| stats values(member) values(gate)... BY <field-they-have-in-common>

But it's limited to 50,000 rows, and will just silently drop everything after 60 seconds or the 50,000 rows is reached.  Please reread that - I mean *silently* drop anything beyond that. At second 60, it's autofinalized and returns whatever it had, even if it's not complete. If it hits result 50,000, it stops right there and returns the 50,000 it had, ignoring any additional rows it could have retrieved.  In both cases, it won't even tell you that it's done so, just stops pulling back results.

But hey, if it's small and quick, it's fine.  🙂

Happy Splunking


0 Karma
Get Updates on the Splunk Community!

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...