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
Get Updates on the Splunk Community!

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...