DB Connect: Why am I getting "Error validating dbmonTail for monitor..." trying to create a database input?

[gajananh999]

Dear All,

I have installed splunk db connect application. I have query from which i have to get the result. when i am running a sql query on database query section in app i am able to get the result.
but when i am trying to create a database input using query i am getting following error

Encountered the following error while trying to save: In handler 'dbx-monitors': Unable to xml-parse the following data: Failed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://bms_ilsts... See splunkd.log for full data.

Can anyone please help me out here where i am going wrong. i am using oracle database.

Thanks

Gajanan Hiroji

[mcronkrite]

Take your SQL and validate that a simpler query works then build it up.
For example the original:

SELECT DISTINCT PRQ.RPT_QUEUE_ID, LRF.FORM_DESC,CU.USER_FULLNAME AS "Scheduled_By", PRQ.SCHEDULED_ON, PRQ.EXECUTION_DATE, 
MAX(PRS.TOTAL) AS "Total_Case_Count" , ROUND((MAX(PRS.END_DATE) - MIN(PRS.START_DATE)) * 3600,2) AS "Run_Time_in_Sec"
FROM ARGUS_APP.PER_RPT_QUEUE PRQ,
ARGUS_APP.PER_RPT_STATUS PRS,
ARGUS_APP.LM_REPORT_FORMS LRF,
ARGUS_APP.CFG_USERS CU

changed to (take away the date fieldsand max and min, if the query runs dbtail, then add on fields one by one. What is the native DB date field format? :

SELECT DISTINCT PRQ.RPT_QUEUE_ID, LRF.FORM_DESC,CU.USER_FULLNAME AS "Scheduled_By",  
MAX(PRS.TOTAL) AS "Total_Case_Count"
FROM ARGUS_APP.PER_RPT_QUEUE PRQ,
ARGUS_APP.PER_RPT_STATUS PRS,
ARGUS_APP.LM_REPORT_FORMS LRF,
ARGUS_APP.CFG_USERS CU

[pmdba]

There are multiple problems here. Rising column should be autofilled by Splunk. Also, you first column returned should be a timestamp, so that Splunk can correctly index the data (in Splunk it's all about when things happen). Read Log File Analysis for Oracle 11g for a primer on how to get information from Oracle into Splunk. Also see this post about date formatting when selecting data from Oracle into Splunk.

A properly constructed dbmon-tail input should look like this:

[dbmon-tail://orcl/scheduler_job_run_details]
 host = localhost
 index = oracle_dbx
 output.format = kv
 output.timestamp = 0
 output.timestamp.format = yyyy-MM-dd HH:mm:ss
 output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
 query = select to_char(log_date,'YYYY-MM-DD HH24:MI:SS') log_date, log_id, owner, job_name, 
 status, error# return_code, to_char(req_start_date,'YYYY-MM-DD HH24:MI:SS') req_start_date,
 to_char(actual_start_date,'YYYY-MM-DD HH24:MI:SS'), actual_start_date, to_char(run_duration) run_duration, 
 instance_id, session_id, to_char(cpu_used) cpu_used, additional_info from dba_scheduler_job_run_details 
 {{WHERE $rising_column$ > ?}}
 sourcetype = job_run_details
 tail.rising.column = LOG_ID
 interval = auto
 table = scheduler_job_run_details

Note that the timestamp formats are defined, the timestamp of the event is converted to match using to_char (and is the first column returned in the query), and rising column is specified in its own parameter and autofilled by Splunk using the $rising_column$ alias. Also note that while I have broken this query across lines for readability, it is better in my experience to place the entire query in a single, unbroken line in your inputs.conf file.

[pmdba]

I don't know if this is a SQL issue or a Splunk input configuration/formatting issue. What does the full entry for the dbmon-tail in your inputs.conf file look like?

[gajananh999]

Thanks you for this suggestion : But no luck

[pradeepkumarg]

Splunk replaces rising column itself, you need not specify it explicitly in the query. Try below.

SELECT DISTINCT PRQ.RPT_QUEUE_ID, LRF.FORM_DESC,CU.USER_FULLNAME AS "Scheduled_By", PRQ.SCHEDULED_ON, PRQ.EXECUTION_DATE, MAX(PRS.TOTAL) AS "Total_Case_Count" , ROUND((MAX(PRS.END_DATE) - MIN(PRS.START_DATE)) * 3600,2) AS "Run_Time_in_Sec" FROM ARGUS_APP.PER_RPT_QUEUE PRQ, ARGUS_APP.PER_RPT_STATUS PRS, ARGUS_APP.LM_REPORT_FORMS LRF, ARGUS_APP.CFG_USERS CU {{ WHERE $rising_column$ > ?}}

And specify your rising column in the field provided below the SQL query "Rising Column*"

[gajananh999]

I think there is some problem with the max and min function we are using in the query,
when i am trying to run this i am getting same error
SELECT PRS.RPT_STATUS_ID, MAX(PRS.TOTAL), ROUND((MAX(PRS.END_DATE) - MIN(PRS.START_DATE)) * 3600,2) from ARGUS_APP.PER_RPT_STATUS PRS {{ WHERE $rising_column$ > ?}}

rising column as RPT_Status_ID

[gajananh999]

Here is the error details
Encountered the following error while trying to save: In handler 'dbx-monitors': Unable to xml-parse the following data: Failed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://bms_ilsts... See splunkd.log for full data.

[gajananh999]

Hello reddy,

Thanks !!!! This work for single table but when you are joining more than one table it won't work...

Whenever i am adding where condition that time it is not working

[adauria_splunk]

I think you just need to change the literal "10" in the last line of your SQL to a literal question mark (?).

The app expects a question mark for the value of the rising column and replaces it with a literal number on the second (and subsequent) runs of the query. On the first run it pulls back the whole table by omitting what's in the double brackets, and stores the initial highest value of the rising column. After that it maintains state.xml with the highest value yet retrieved, and plugs that in for the ? each time it runs.

[gajananh999]

Hello Andrew,
Thanks of the reply i tried this but same error.

[gajananh999]

Not able to run simple query also
SELECT RPT_QUEUE_ID FROM ARGUS_APP.PER_RPT_QUEUE PRQ {{ WHERE $RPT_QUEUE_ID$ > 10 }}

[gajananh999]

My sql is here SELECT DISTINCT PRQ.RPT_QUEUE_ID, LRF.FORM_DESC,CU.USER_FULLNAME AS "Scheduled_By", PRQ.SCHEDULED_ON, PRQ.EXECUTION_DATE,
MAX(PRS.TOTAL) AS "Total_Case_Count" , ROUND((MAX(PRS.END_DATE) - MIN(PRS.START_DATE)) * 3600,2) AS "Run_Time_in_Sec"
FROM ARGUS_APP.PER_RPT_QUEUE PRQ,
ARGUS_APP.PER_RPT_STATUS PRS,
ARGUS_APP.LM_REPORT_FORMS LRF,
ARGUS_APP.CFG_USERS CU
{{WHERE $RPT_QUEUE_ID$ > 10}}