topic Re: DBTail issue. in Deployment Architecture

DBTail issue.

theouhuios — Fri, 20 Sep 2013 00:17:28 GMT

Hello

I am trying to us dbtail to get a query working and its not working. The rising column name is modifiedTime. I dont know where the mistake is. Any help please.

  SELECT to_char(I.SYSMODTIME,'MM/DD/YYYY HH24:MI:SS') as "modifiedTime",to_char(I.OPEN_TIME,'MM/DD/YYYY HH24:MI:SS') as "createdTime", xxxxx  where A."NAME" = I.ASSIGNMENT and P.CONTACT_NAME = A.WDMANAGERNAME and I.LOGICAL_NAME = C.LOGICAL_NAME WHERE $rising_column$ < dateadd(minute,-1,getdate()) {{AND $rising_column$ > ?}} ORDER BY $rising_column$

Adding the configs for this to make sure that I am doing it the right way.

  [dbmon-tail://xxxx/xxx]
    output.format = kv
    output.timestamp = true
    output.timestamp.column = modifiedTime
    sourcetype =xxxx
    query = xxx
    tail.rising.column = modifiedTime
    index = itsm
    interval = 15m

I see that there is error in the query

2013-09-19 18:41:00.706 dbx8500:ERROR:TailDatabaseMonitor - Configuration Error: Invalid query specified! Missing placeholder for condition!
2013-09-19 18:41:00.706 dbx8500:INFO:TailDatabaseMonitor - Database monitor=[dbmon-tail://SM:SMPRD93/incident] finished with status=false resultCount=0 in duration=9 ms
2013-09-19 18:41:00.706 dbx8500:INFO:ExecutionContext - Execution finished in duration=9 ms
2013-09-19 18:41:00.707 monsch1:INFO:Scheduler - Execution of input=[dbmon-tail://SM:SMPRD93/incident] finished in duration=9 ms with resultCount=0 success=false continueMonitoring=false

Re: DBTail issue.

jcoates_splunk — Fri, 20 Sep 2013 00:54:44 GMT

Hi,

what's the SQL data type for the modifiedTime column?

Re: DBTail issue.

theouhuios — Fri, 20 Sep 2013 01:23:05 GMT

I am sorry, I am not that good with SQL. I didn't get your question actually. Can you please rephrase that.

Re: DBTail issue.

jcoates_splunk — Fri, 20 Sep 2013 01:49:30 GMT

Hi, in SQL each column has a type. if your modifiedTime column is actually not a datetime type, your dateadd function won't work.

Re: DBTail issue.

theouhuios — Fri, 20 Sep 2013 02:11:35 GMT

It is a Datetim type. As you can see from the full query its being renamed to modifiedTime. Do I need to use the default field name for that to work?

Re: DBTail issue.

lukejadamec — Fri, 20 Sep 2013 02:30:52 GMT

The query and input are not the same.
Try the input without the ORDER BY statement, and post the error if any from the splunk\var\log\splunk\dbx.log

Re: DBTail issue.

theouhuios — Fri, 20 Sep 2013 15:05:08 GMT

It doesn't give any error but it also doesn't give any results for it.

Re: DBTail issue.

lukejadamec — Fri, 20 Sep 2013 15:07:30 GMT

When you restart the db input from db connect, what messages are you seeing in the dbx log? You should also check the splunkd.log

Re: DBTail issue.

theouhuios — Fri, 20 Sep 2013 15:22:03 GMT

There is a Invalid query error.Added the update for it.

Re: DBTail issue.

lukejadamec — Mon, 28 Sep 2020 14:49:24 GMT

If you're query is still the same as shown above, then I have some recommendations:
1) don't use $rising_column$ in your query except at the end.
2) the last line should read exactly:
{{WHERE $rising_column$ > ?}}
3) Once you get it to work, then try to modify it - start simple because then it is easier to see what is breaking it.