All Apps and Add-ons

DB Connect: How union 2 dbxquery from 2 different tables

Luninho
Explorer

I have 2 searches:

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

2) |dbxquery query="select description from fo.date 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

Richfez
SplunkTrust
SplunkTrust

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

-Rich

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!