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?
... View more
