All Apps and Add-ons

DB Connect Sql Query Error : Why am I getting the following error when trying to join tables?

batsonpm
Path Finder

I'm trying to join a few tables from a sql database. (Works fine in SQL)

The tables are: Interfaces, InterfaceTraffic, and Nodes

"Nodes" are the devices, there are multiple "Interfaces" on a "Node". "InterfaceTraffic" has the stats on the "Interfaces". I want to join the stats to the "Interfaces", then join the "Interfaces" to the "Nodes".

If I run the query on either table separately without a join, I get results. If I try to join the tables, I get a "Column Name Conflicted" error: java.lang.IllegalStateException: Column name conflicted, please set shortnames option to false and retry

I don't understand how there can be a conflict. I've been digging through the documentation, and I don't see what I'm missing.
It seems like it is some kind of simple Splunk syntax that I am missing.

Thanks for any help!!

 SELECT Interfaces.NodeID,
         Interfaces.InterfaceID,
         Interfaces.InterfaceName,
         Interfaces.InterfaceIndex,
         Interfaces.InterfaceType,
         Interfaces.InterfaceTypeDescription,
         Interfaces.InterfaceSpeed,
         Interfaces.InterfaceMTU,
         Interfaces.PhysicalAddress,
         Interfaces.FullName,
         Interfaces.Status,
         Interfaces.Outbps,
         Interfaces.Inbps,
         Interfaces.OutPercentUtil,
         Interfaces.InPercentUtil,
         Interfaces.InDiscardsThisHour,
         Interfaces.InDiscardsToday,
         Interfaces.InErrorsThisHour,
         Interfaces.InErrorsToday,
         Interfaces.OutDiscardsThisHour,
         Interfaces.OutDiscardsToday,
         Interfaces.OutErrorsThisHour,
         Interfaces.OutErrorsToday,
         Interfaces.MaxInBpsToday,
         Interfaces.MaxInBpsTime,
         Interfaces.MaxOutBpsToday,
         Interfaces.MaxOutBpsTime,
         Interfaces.LastSync,
         InterfaceTraffic.NodeID,
         InterfaceTraffic.InterfaceID,
         InterfaceTraffic.DateTime,
         InterfaceTraffic.In_Averagebps,
         InterfaceTraffic.In_Minbps,
         InterfaceTraffic.In_Maxbps,
         InterfaceTraffic.In_TotalBytes,
         InterfaceTraffic.In_TotalPkts,
         InterfaceTraffic.Out_Averagebps,
         InterfaceTraffic.Out_Minbps,
         InterfaceTraffic.Out_Maxbps,
         InterfaceTraffic.Out_TotalBytes,
         InterfaceTraffic.Out_TotalPkts,
         Nodes.NodeID,
         Nodes.IP_Address,
         Nodes.Community,
         Nodes.SysName,
         Nodes.Location,
         Nodes.StatusDescription,
         Nodes.Status,
         Nodes.LastBoot,
         Nodes.SystemUpTime,
         Nodes.ResponseTime,
         Nodes.PercentLoss,
         Nodes.AvgResponseTime,
         Nodes.MinResponseTime,
         Nodes.MaxResponseTime,
         Nodes.LastSync,
         Nodes.City,
         Nodes.Business_Unit,
         Nodes.Site_Name
FROM "SolarWindsOrion"."dbo"."Interfaces"
JOIN "SolarWindsOrion"."dbo"."InterfaceTraffic"
    ON InterfaceTraffic.InterfaceID = Interfaces.InterfaceID
JOIN "SolarWindsOrion"."dbo"."Nodes"
    ON Nodes.NodeID = Interfaces.NodeID
0 Karma
1 Solution

batsonpm
Path Finder

Ok, so what I figured out was that Splunk drops the table name and leaves the field name. So any of the fields listed, even though they are in different tables require unique names. So I went through and changed the field names for the ones that were duplicates and it works!! The following changes were made to the previous list.

So technically a syntax issue again that I have stumbled on because of my inexperience. Hope this helps others if they run across this error.

SELECT Interfaces.NodeID AS IFNodeID,
         Interfaces.InterfaceID AS IFIFID,
         Interfaces.Status AS IFStatus,
         Interfaces.LastSync AS IFLastSync,
         InterfaceTraffic.NodeID AS IFTNodeID,
         InterfaceTraffic.InterfaceID AS IFTIFID,
         Nodes.NodeID AS NodeNodeID,
         Nodes.Status AS NodeStatus,
         Nodes.LastSync AS NodeLastSync

View solution in original post

batsonpm
Path Finder

Ok, so what I figured out was that Splunk drops the table name and leaves the field name. So any of the fields listed, even though they are in different tables require unique names. So I went through and changed the field names for the ones that were duplicates and it works!! The following changes were made to the previous list.

So technically a syntax issue again that I have stumbled on because of my inexperience. Hope this helps others if they run across this error.

SELECT Interfaces.NodeID AS IFNodeID,
         Interfaces.InterfaceID AS IFIFID,
         Interfaces.Status AS IFStatus,
         Interfaces.LastSync AS IFLastSync,
         InterfaceTraffic.NodeID AS IFTNodeID,
         InterfaceTraffic.InterfaceID AS IFTIFID,
         Nodes.NodeID AS NodeNodeID,
         Nodes.Status AS NodeStatus,
         Nodes.LastSync AS NodeLastSync

richgalloway
SplunkTrust
SplunkTrust

Because of the way DBX transmogrifies your query (see dbx*.log), what you enter is not exactly what gets executed. Try to make changes to your query based on what you find in the logs, but the best solution probably will be to great a view that does the join for you.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...