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
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.
There is a Invalid query error.Added the update for it.
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
It doesn't give any error but it also doesn't give any results for it.
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
Hi,
what's the SQL data type for the modifiedTime column?
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?
Hi, in SQL each column has a type. if your modifiedTime column is actually not a datetime type, your dateadd function won't work.
I am sorry, I am not that good with SQL. I didn't get your question actually. Can you please rephrase that.