Splunk DB Connect 1: How do you get dbquery to recognize applied field names in a SQL SELECT statement where there is no transform command like COUNT or SUM?

pepper_seattle — Thu, 05 May 2016 21:56:29 GMT

Issue:
Renaming a field in SQL that is not transformed in some way (transformations: COUNT() AS or SUM() AS , etc.) will leave the field named as it is in the table, making the addition of multiple fields with the same name from different tables impossible.

Example:

| dbquery TEST " 
SELECT DATE(ab.reportdate) AS reportdate, ab.name AS device_A, cd.name AS device_B, cd.deviceid, 
FROM testing 
JOIN application.devicetype as cd ON ab.deviceid = cd.deviceid
....;"

In this example, the field ab.name will show up in the results as name not under the new name device_A and cd.name won't show up at all as the 'name' column already exists.

Question:
How do you get dbquery to recognize applied field names in a SELECT statement where there is not a transform command like COUNT or SUM?

Re: Splunk DB Connect 1: How do you get dbquery to recognize applied field names in a SQL SELECT statement where there is no transform command like COUNT or SUM?

Richfez — Thu, 05 May 2016 23:55:20 GMT

There is a bug? Maybe just "unintended feature?" of dbconnect that it doesn't seem to honor aliases unless certain hoops have been jumped through. So, in your case, fake it out with easy hoops. One of the more common methods is to cast them all to character strings, like

SELECT DATE(reportdate) AS reportdate, CAST(description AS CHAR(64)) AS Description....

And so on.

topic Re: Splunk DB Connect 1: How do you get dbquery to recognize applied field names in a SQL SELECT statement where there is no transform command like COUNT or SUM? in All Apps and Add-ons

Splunk DB Connect 1: How do you get dbquery to recognize applied field names in a SQL SELECT statement where there is no transform command like COUNT or SUM?

Re: Splunk DB Connect 1: How do you get dbquery to recognize applied field names in a SQL SELECT statement where there is no transform command like COUNT or SUM?