topic Re: DB Connect Database Query Ignoring Alias Names and Functions in Splunk Search

DB Connect Database Query Ignoring Alias Names and Functions

AvianFLU — Mon, 28 Sep 2020 17:09:38 GMT

Hey folks,

I'm running into an issue where the Splunk DB Connect App is not respecting the alias names and through my debugging, also functions.

My original query tested via MySQL Workbench returns everything as intended.

SELECT p.bf_description AS ProjectName, s.bf_name AS SelectorName, FROM_UNIXTIME(b.bf_start) AS BuildStart, b.bf_duration AS BuildDuration,b.bf_state, bf_cancelled, b.bf_tag,bf_user_id, b.bf_result AS OverallBuildResult, b.bf_passchain_id, b.bf_failchain_id, bf_chain_id, bf_ischained, b.bf_modified FROM bf_builds b LEFT JOIN bf_projects p ON b.bf_project_id=p.bf_id LEFT JOIN bf_selector s ON b.bf_selector_id=s.bf_id WHERE b.bf_modified > SUBDATE(NOW(), INTERVAL 1 HOUR)

My modified query for DB Connect App after various iterations of testing:

SELECT CAST(p.bf_description AS CHAR(64)) AS ProjectName, s.bf_name AS ServerName, r.bf_result AS Result, r.bf_description AS StepName, r.bf_seq AS StepNumber, FROM_UNIXTIME(r.bf_start) AS StartTime, r.bf_duration AS Duration, (commands.bf_text) AS Commands, r.bf_log_id, r.bf_step_id, r.bf_modified FROM bf_results r LEFT JOIN bf_servers s ON r.bf_server_id=s.bf_id LEFT JOIN bf_builds b ON r.bf_build_id=b.bf_id LEFT JOIN bf_projects p ON b.bf_project_id=p.bf_id LEFT JOIN bf_store commands ON r.bf_command_store=commands.bf_id WHERE r.bf_modified > SUBDATE(NOW(), INTERVAL 1 HOUR)

In the query above, the only way I can get the DB Connect App to respect Alias names is to use a MySQL function. For testing purposes, I simply casted a varchar type as an explicit char:

CAST(p.bf_description AS CHAR(64)) AS ProjectName

This resulted in the returning query to respect the Alias name 'ProjectName'. Similarly the following code also triggered the DB Connect App to respect the Alias name StartTime:

FROM_UNIXTIME(r.bf_start) AS StartTime

However, the latter function is supposed to convert the r.bf_start value to a legitimate DateTime return value. This works in MySQL Workbench, but the DB Connect App returned the original unixtime value.

Does anyone have any ideas?

Re: DB Connect Database Query Ignoring Alias Names and Functions

jcoates_splunk — Thu, 24 Jul 2014 02:25:52 GMT

Just a guess, but because that's a time column it might have extra work being applied to it. What happens if you cast it as DATETIME?

Re: DB Connect Database Query Ignoring Alias Names and Functions

AvianFLU — Mon, 28 Sep 2020 17:10:38 GMT

Not really an answer, but I found a workaround.

Intentionally casting every single column as char will result in the Alias names being respected. My final query is as follows:

SELECT CAST(p.bf_description AS char(128)) AS ProjectName, CAST(s.bf_name AS CHAR(32)) AS ServerName, CAST(r.bf_result AS CHAR(1)) AS Result, CAST(r.bf_description AS CHAR(128)) AS StepName, CAST(r.bf_seq AS CHAR(3)) AS StepNumber, CAST(FROM_UNIXTIME(r.bf_start) AS CHAR(64)) AS StartTime, CAST(r.bf_duration AS CHAR(32)) AS Duration, commands.bf_text AS Commands, r.bf_log_id, r.bf_step_id, CAST(r.bf_modified AS CHAR(64)) AS Modified FROM bf_results r LEFT JOIN bf_servers s ON r.bf_server_id=s.bf_id LEFT JOIN bf_builds b ON r.bf_build_id=b.bf_id LEFT JOIN bf_projects p ON b.bf_project_id=p.bf_id LEFT JOIN bf_store commands ON r.bf_command_store=commands.bf_id WHERE r.bf_modified > SUBDATE(NOW(), INTERVAL 1 HOUR)