MySQL and Splunk - Select and Join

Question

I am having an issue with the code below when setting up a DBconnect query inside splunk.

SELECT * FROM master_biz.legend_asset
RIGHT JOIN 
master_custom.custom_app_table_4 
ON 
master_custom.custom_app_table_4.ID = master_biz.legend_asset.ID

When I use the code above, it executes perfectly from within PHPmyAdmin. However, when I try to use it in Splunk I get an error that states:

Invalid Query
External search command 'dbxquery' returned error code 1. Script output =
"RuntimeError: Failed to run query: "SELECT * FROM (SELECT * FROM
master_biz.legend_asset RIGHT JOIN master_custom.custom_app_table_4 ON 
master_custom.custom_app_table_4.ID = master_biz.legend_asset.ID) t", caused     
by:AvroRemoteException(u"com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException : Duplicate column name 'id'",). 

This error is stating that I have duplicate columns called "ID". I figured this would be a perfect time to clean up some of the data, so I attempted to rename the ID field as seen below:

SELECT 
 master_biz.legend_asset.roa_id AS ORGANIZATION_NUMBER,
 master_biz.legend_asset.make AS MANUFACTURER,
 master_biz.legend_asset.model AS PRODUCT,
 master_biz.legend_asset.status AS STATUS,
 master_biz.legend_asset.ID AS ASSET_ID
FROM master_biz.legend_asset
RIGHT JOIN master_custom.custom_app_table_4 ON 
master_custom.custom_app_table_4.ID = master_biz.legend_asset.ID

However, when I try this query, I am left only with the fields I renamed and none of the fields from the custom_app_table_4. Thinking this may be due to the renaming of the ID field I changed the query to this:

SELECT 
 master_biz.legend_asset.roa_id AS ORGANIZATION_NUMBER,
 master_biz.legend_asset.make AS MANUFACTURER,
 master_biz.legend_asset.model AS PRODUCT,
 master_biz.legend_asset.status AS STATUS,
 master_biz.legend_asset.ID AS ASSET_ID
FROM master_biz.legend_asset
RIGHT JOIN master_custom.custom_app_table_4 ON 
master_custom.custom_app_table_4.ID = master_biz.legend_asset.ASSET_ID

This resulted in the following error:

#1054 - Unknown column master_biz.legend_asset.ASSET_ID in on clause

master_biz.legend_asset Table

<style type="text/css">
  table.tableizer-table {
    font-size: 12px;
    border: 1px solid #CCC;
    font-family: Arial, Helvetica, sans-serif;
  }
  .tableizer-table td {
    padding: 4px;
    margin: 3px;
    border: 1px solid #CCC;
  }
  .tableizer-table th {
    background-color: #104E8B;
    color: #FFF;
    font-weight: bold;
  }
</style>
<table class="tableizer-table">
  <thead>
    <tr class="tableizer-firstrow">
      <th>id</th>
      <th>did</th>
      <th>roa_id</th>
      <th>make</th>
      <th>model</th>
      <th>type</th>
      <th>function</th>
      <th>status</th>
      <th>owner</th>
      <th>serial</th>
      <th>asset_tag</th>
      <th>rfid</th>
      <th>date_edit</th>
      <th>user_edit</th>
      <th>a_notes</th>
      <th>owner_admin</th>
      <th>owner_tech</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>2</td>
      <td>0</td>
      <td>1</td>
      <td>Tenable</td>
      <td>Nessus</td>
      <td>&nbsp;</td>
      <td>Unknown</td>
      <td>Production</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>NULL</td>
      <td>&nbsp;</td>
      <td>5/23/2016 16:19</td>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>3</td>
      <td>0</td>
      <td>1</td>
      <td>Tenable</td>
      <td>Nessus</td>
      <td>&nbsp;</td>
      <td>Unknown</td>
      <td>Production</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>NULL</td>
      <td>&nbsp;</td>
      <td>5/20/2016 18:59</td>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>4</td>
      <td>0</td>
      <td>2</td>
      <td>Microsoft</td>
      <td>Windows Server Standard 2012 R2</td>
      <td>&nbsp;</td>
      <td>Unknown</td>
      <td>Production</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>NULL</td>
      <td>&nbsp;</td>
      <td>5/20/2016 18:59</td>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>5</td>
      <td>0</td>
      <td>0</td>
      <td>Solarwinds</td>
      <td>Kiwi CAT Tools</td>
      <td>&nbsp;</td>
      <td>Unknown</td>
      <td>Production</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>NULL</td>
      <td>&nbsp;</td>
      <td>5/20/2016 18:59</td>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>6</td>
      <td>0</td>
      <td>1</td>
      <td>Splunk</td>
      <td>Enterprise</td>
      <td>&nbsp;</td>
      <td>Unknown</td>
      <td>Production</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>NULL</td>
      <td>&nbsp;</td>
      <td>5/20/2016 18:59</td>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>7</td>
      <td>0</td>
      <td>1</td>
      <td>Splunk</td>
      <td>Enterprise Support</td>
      <td>&nbsp;</td>
      <td>Unknown</td>
      <td>Production</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>NULL</td>
      <td>&nbsp;</td>
      <td>5/23/2016 16:19</td>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>8</td>
      <td>0</td>
      <td>1</td>
      <td>VMware</td>
      <td>vSphere 5/6 Support Standard</td>
      <td>&nbsp;</td>
      <td>Unknown</td>
      <td>Production</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>NULL</td>
      <td>&nbsp;</td>
      <td>5/20/2016 18:59</td>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>9</td>
      <td>0</td>
      <td>1</td>
      <td>VMware</td>
      <td>vSphere 5/6 Support Enterprise Plus</td>
      <td>&nbsp;</td>
      <td>Unknown</td>
      <td>Production</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>NULL</td>
      <td>&nbsp;</td>
      <td>5/20/2016 18:59</td>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>10</td>
      <td>0</td>
      <td>1</td>
      <td>VMware</td>
      <td>vCenter 5/6 Support Standard</td>
      <td>&nbsp;</td>
      <td>Unknown</td>
      <td>Production</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>NULL</td>
      <td>&nbsp;</td>
      <td>5/20/2016 18:59</td>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>0</td>
    </tr>
  </tbody>
</table>

master_biz.asset_location Table

<style type="text/css">
  table.tableizer-table {
    font-size: 12px;
    border: 1px solid #CCC;
    font-family: Arial, Helvetica, sans-serif;
  }
  .tableizer-table td {
    padding: 4px;
    margin: 3px;
    border: 1px solid #CCC;
  }
  .tableizer-table th {
    background-color: #104E8B;
    color: #FFF;
    font-weight: bold;
  }
</style>
<table class="tableizer-table">
  <thead>
    <tr class="tableizer-firstrow">
      <th>iid</th>
      <th>location</th>
      <th>floor</th>
      <th>room</th>
      <th>plate</th>
      <th>panel</th>
      <th>punch</th>
      <th>zone</th>
      <th>rack</th>
      <th>shelf</th>
      <th>date_edit</th>
      <th>user_edit</th>
      <th>l_notes</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Lab</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Production</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>4</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>5</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>6</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>7</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>8</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>9</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>10</td>
      <td>&nbsp;</td>
      <td>0</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0000-00-00 00:00:00</td>
      <td>1</td>
      <td></td>
    </tr>
  </tbody>
</table>

master_custom.custom_app_table_4 Table

<style type="text/css">
  table.tableizer-table {
    font-size: 12px;
    border: 1px solid #CCC;
    font-family: Arial, Helvetica, sans-serif;
  }
  .tableizer-table td {
    padding: 4px;
    margin: 3px;
    border: 1px solid #CCC;
  }
  .tableizer-table th {
    background-color: #104E8B;
    color: #FFF;
    font-weight: bold;
  }
</style>
<table class="tableizer-table">
  <thead>
    <tr class="tableizer-firstrow">
      <th>id</th>
      <th>2_2</th>
      <th>8_2</th>
      <th>9_2</th>
      <th>10_2</th>
      <th>11_2</th>
      <th>12_2</th>
      <th>13_2</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>2</td>
      <td>Software License</td>
      <td>Tenable</td>
      <td>Professional</td>
      <td>1</td>
      <td>5/10/2017</td>
      <td>2190</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Software License</td>
      <td>Tenable</td>
      <td>Professional</td>
      <td>1</td>
      <td>5/10/2017</td>
      <td>2190</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>4</td>
      <td>Software License</td>
      <td>Microsoft</td>
      <td>Standard</td>
      <td>10</td>
      <td>5/3/2016</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>5</td>
      <td>Software Maintenance</td>
      <td>Solarwinds</td>
      <td>N/A</td>
      <td>4</td>
      <td>10/30/2016</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>6</td>
      <td>Software License</td>
      <td>Splunk</td>
      <td>20GB</td>
      <td>1</td>
      <td>6/1/2016</td>
      <td>60000</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>7</td>
      <td>Software Maintenance</td>
      <td>Splunk</td>
      <td>Enterprise</td>
      <td>1</td>
      <td>6/1/2016</td>
      <td>0</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>8</td>
      <td>Software Maintenance</td>
      <td>VMware</td>
      <td>24x7 Production</td>
      <td>30</td>
      <td>5/10/2017</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>9</td>
      <td>Software Maintenance</td>
      <td>VMware</td>
      <td>Subscription Only</td>
      <td>46</td>
      <td>5/10/2017</td>
      <td>4375</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>10</td>
      <td>Software Maintenance</td>
      <td>VMware</td>
      <td>Subscription Only</td>
      <td>3</td>
      <td>5/10/2017</td>
      <td>530</td>
      <td></td>
    </tr>
  </tbody>
</table>

Ultimately I have several obstacles I am trying to overcome with a single question. I would like to join select columns from different tables within multiple databases and rename the columns to something that is more logical.

So in short, I would like to be able to merge these three tables into one table using an SQL query. If possible, it would be nice to change the names of some of the fields so that the data is easier to interpret. It would be awesome to be able to only select the fields I want from each table.

By combining these tables and selecting only the fields that are of interest, it will save on the daily usage license inside splunk, disk storage space, as well as cpu utilization during search.

Any help is greatly appreciated.

Answer 1

SELECT * is antipattern. If id is only column that exists in both tables you could use:

SELECT *
FROM master_biz.legend_asset
RIGHT JOIN master_custom.custom_app_table_4 
  USING (id);

Otherwise you need to add alias for each column manually:

SELECT a.ID    AS id
       ,a. ... AS ...
       ,t4.col AS ...  
FROM master_biz.legend_asset a
RIGHT JOIN master_custom.custom_app_table_4 t4
  ON a.ID = t4.ID;

Note: You don't need to write table name, you could use table aliases.

EDIT:

what are the differences in the JOIN ON and JOIN USING parts of the code?

USING will return columns that are used in JOIN once:

SELECT *
FROM t1
JOIN t2
  USING(i);

SELECT *
FROM t1
JOIN t2
  ON t1.i = t2.i;

SqlFiddleDemo

Output:

╔════╦════╦═══╗
║ i  ║ b  ║ c ║
╠════╬════╬═══╣
║ 1  ║ 1  ║ 3 ║
╚════╩════╩═══╝

vs.

╔════╦════╦════╦═══╗
║ i  ║ b  ║ i  ║ c ║
╠════╬════╬════╬═══╣
║ 1  ║ 1  ║ 1  ║ 3 ║
╚════╩════╩════╩═══╝