Splunk Enterprise 6.5.1 - yes, an upgrade is planned!
I have a series of equivalent dbxqueries in a Splunk dashboard panel, against two flavours of schema, whose basic form is:
<query>| dbxquery query="SELECT 'GAMMA_JETSONS' as CLIENT,
(SELECT GROUP_CONCAT(DISTINCT SOURCE order by SOURCE asc , ' , ' ) as source from gamma_jetsons.live_data) AS FEEDS_TODAY,
(SELECT COUNT(DISTINCT SOURCE) from gamma_jetsons.live_data) AS CURRENT,
(SELECT COUNT(DISTINCT SOURCE) from gamma_jetsons.live_data_archived) AS YESTERDAY,
(SELECT GROUP_CONCAT(DISTINCT SOURCE order by SOURCE asc , ' , ' ) as source from gamma_jetsons.live_data_archived) AS FEEDS_YESTERDAY
FROM DUAL;"
connection="PROD_GAMMA_JETSONS" shortnames=1 | fields - _raw, _time</query>
In order to make it easier to change schema and add new rows for new databases I have tried to parameterise this using MySQL Prepared Statements, which (escaped for quotation marks and semicolons and less-than symbols) is:
<query>| dbxquery query="SET @my_cust:='GAMMA_JETSONS';
SET @my_custid:='jetsons';
SET @t1s:=CONCAT("SELECT IF((SELECT COUNT(SCHEMA_NAME) from information_schema.schemata WHERE SCHEMA_NAME='",@my_custid,"_engine_persist_service') < 1,'gamma_",@my_custid,".live_data','",@my_custid,"_engine_persist_service.live_data') INTO @t1;");
SET @t2s:=CONCAT("SELECT IF((SELECT COUNT(SCHEMA_NAME) from information_schema.schemata WHERE SCHEMA_NAME='",@my_custid,"_engine_persist_service') < 1,'gamma_",@my_custid,".live_data_archived','",@my_custid,"_engine_persist_service.live_data_archived') INTO @t2;");
PREPARE stmt FROM @t1s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @t2s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @my_sql:= CONCAT("SELECT '",@my_cust,"' as CLIENT,
(SELECT GROUP_CONCAT(DISTINCT source ORDER BY source ASC) AS source FROM ",@t1,") AS FEEDS_TODAY,
(SELECT COUNT(DISTINCT SOURCE) FROM ",@t1,") AS CURRENT,
(SELECT COUNT(DISTINCT SOURCE) FROM ",@t2,") AS YESTERDAY,
(SELECT GROUP_CONCAT(DISTINCT source ORDER BY source ASC) AS source FROM ",@t2,") AS FEEDS_YESTERDAY
FROM DUAL;");
PREPARE stmt FROM @my_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;"
connection="PROD_GAMMA_JETSONS" shortnames=1 | fields - _raw, _time</query>
The unescaped version works fine in MySQL using e.g. HeidiSQL:
SET @my_cust:='GAMMA_JETSONS';
SET @my_custid:='jetsons';
SET @t1s:=CONCAT("SELECT IF((SELECT COUNT(SCHEMA_NAME) from information_schema.schemata WHERE SCHEMA_NAME='",@my_custid,"_engine_persist_service') < 1,'gamma_",@my_custid,".live_data','",@my_custid,"_engine_persist_service.live_data') INTO @t1;");
SET @t2s:=CONCAT("SELECT IF((SELECT COUNT(SCHEMA_NAME) from information_schema.schemata WHERE SCHEMA_NAME='",@my_custid,"_engine_persist_service') < 1,'gamma_",@my_custid,".live_data_archived','",@my_custid,"_engine_persist_service.live_data_archived') INTO @t2;");
PREPARE stmt FROM @t1s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @t2s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @my_sql:= CONCAT("SELECT '",@my_cust,"' as CLIENT,
(SELECT GROUP_CONCAT(DISTINCT source ORDER BY source ASC) AS source FROM ",@t1,") AS FEEDS_TODAY,
(SELECT COUNT(DISTINCT SOURCE) FROM ",@t1,") AS CURRENT,
(SELECT COUNT(DISTINCT SOURCE) FROM ",@t2,") AS YESTERDAY,
(SELECT GROUP_CONCAT(DISTINCT source ORDER BY source ASC) AS source FROM ",@t2,") AS FEEDS_YESTERDAY
FROM DUAL;");
PREPARE stmt FROM @my_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
In Splunk all I get is an error in the panel's header row:
error_message=A value for dbxquery command option connection is required
I can't see what the issue might be. Am I missing something, or are Prepared Statements and multi-statement queries of this kind not supported?
Thanks.
... View more