Hello all,
I'm new to creating monitors in Orion and having difficulty with writing a query that the Oracle User Experience Monitor will accept. Is there someone who could explain how the SQL query is passed from Orion to Oracle? Or point me to where this is documented? I've read the SolarWinds documentation, but haven't really found what I'm looking for.
Below are the queries as they stand today. Any help is appreciated.
--this query errors with "ORA-00911: invalid character"
ALTER SESSION SET nls_date_format='MM/DD/YYYY HH24:MI:SS';
ALTER SESSION SET nls_timestamp_format='MM/DD/YYYY HH24:MI:SS';
SELECT ROUND((SYSDATE - TO_DATE(MAX(created),'MM/DD/YYYY HH24:MI:SS')),2) * 1440 AS Minutes
FROM dps.dt_customer WHERE SYSTEM_ID=782
--have not tested in Orion yet. Will this work?
BEGIN
curr_date VARCHAR2(4);
param_value ems.parameterid%TYPE;
orion_val BINARY;
SELECT to_char(CURRENT_TIMESTAMP,'HH24MM') INTO curr_date FROM dual;
SELECT PARAMETERVALUE into param_value from ems.systemparameter where parameterid = 'DISABLE';
IF (curr_date > '0400' and curr_date < '2100') THEN
IF param_value = 'Y' THEN
orion_val := 1; --TRUE : ems.systemparameter.parameterid = 'Y'
ELSE IF param_value = 'N'
THEN
orion_val := 0; --FALSE : ems.systemparameter.parameterid = 'N'
ELSE
orion_val := 0; --FALSE : ems.systemparameter.parameterid != 'Y' OR 'N'
END IF;
ELSE
END IF;
RETURN orion_val;
END;