DBX JOIN 2 databases together

[hartfoml]

I can write a search like this:

| dbquery "DB1" "SELECT A.* AOS.* FROM Assets A JOIN AssetOSs AOS ON A.AssetOSID = AOS.AssetOSID" | Search *

How would I do a dbquery Join Statement for two different databases???

| dbquery "DB1|DB2" "SELECT DB1A.IPAddressStr DB2M.User FROM DB1.Assets DB1A JOIN DB2.Machines DB2M ON DB1A.IPAddressStr = DB2M.IP"

This doesn't work!!

Any help would be great

[linu1988 · Accepted solution]

we should not do any join and let the data come to splunk. It is wastage of resource and not proper usage of the database engine itself

No need to include any other db in the query. Just put one single query and then write down the query using conventional join. Just include the full name, DB.schema.table name. it will work

[pmdba]

This assumes that the databases are similar, and that you can select all the data you want from a single database (i.e. that the two databases are linked apart from Splunk). If the data sources from different database types (e.g. Oracle and MSSQL) this will not work. If the two databases are not linked in some way to allow one to query the other, then joining the data in Splunk is your only option, even if it's not the most efficient way to do things.

[linu1988]

the database engine is more powerful than Splunk's query engine as database knows how the data is stored and retrieved. Selecting them both then do a join in splunk is not proper use of network.The desired data can be taken from the DB directly and then with Splunk's command you can manipulate

For e.g. if you have 10 Million records in both the tables and the desired result is only 30000 records then in DB you only see 20 Million.But when you do it in splunk you are taking 20+20 Million to the join,which takes more time and resource.But joining DB data with splunk's raw data will be useful

[hartfoml]

Can you explain briefly what you mean by "wastage of resource and not proper usage of the database engine" For use no SQL types.

[pmdba]

See this previous post: http://answers.splunk.com/answers/76183/splunk-db-connect-multiple-db-join