Archive

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
Path Finder

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?

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

Richfez
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!