DBTail issue.

[theouhuios]

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

[lukejadamec]

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.

[theouhuios]

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

[lukejadamec]

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

[theouhuios]

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

[lukejadamec]

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

[jcoates_splunk]

Hi,

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

[theouhuios]

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?

[jcoates_splunk]

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

[theouhuios]

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