Hi 🙂
I can successfully connect to a MSSQL DB and run adhoc SQL queries on it from within DB Connect, but when I try to schedule an Input "Tail" (or Dump) it fails with the following error in the dbx.log :-
2014-03-26 08:37:44.776 monsch26:ERROR:Scheduler - Error while reading stanza=[dbmon-tail://Foo_Bar/Foo_Bar_TblBets]: com.splunk.config.SplunkConfigurationException: Error instantiating output format kv: java.lang.IllegalArgumentException: Illegal pattern character 'N'
I have tried using kv and csv for the Output Format but I still get the same error.
Here are the settings in inputs.conf :-
[dbmon-tail://Foo_Bar/Foo_Bar_TblBets]
host = Foo
index = test
interval = 5m
output.format = kv
output.timestamp = 1
output.timestamp.column = BetDate
query = select Top 100 BetDate, BetID, ExternalBetID, Settled, ExternalClientID, AmountToWIn from TblBets (NOLOCK) Where ExternalBetID Is Not Null And Channel = 200 {{And $rising_column$ > ?}}\r\n
sourcetype = sqltest
tail.rising.column = BetID
table = Foo_Bar_TblBets
output.timestamp.format = %s.%3N
disabled = 0
Example output from dbquery:
BetID BetDate ExternalBetID Settled ExternalClientID AmountToWIn
1 983738389 1341334616.453 67650588 true XYZ 4.0000
2 983915715 1341397843.650 67662499 true XYZ 10.0000
Thanks in advance,
Luke 🙂
Your output.timestamp.format is strptime. Java (and by proxy DB Connect) expects the SimpleDateFormat notation:
http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html
http://stackoverflow.com/questions/4142313/java-convert-milliseconds-to-time-format
Other answers recommend converting from epoch to an alternative timestamp in your SQL:
Your output.timestamp.format is strptime. Java (and by proxy DB Connect) expects the SimpleDateFormat notation:
http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html
http://stackoverflow.com/questions/4142313/java-convert-milliseconds-to-time-format
Other answers recommend converting from epoch to an alternative timestamp in your SQL:
Thanks araitz!
I updated the SQL query to convert the datetime to something human readable and now we are Splunking hard!!! 🙂
query = select Top 100 CONVERT(varchar(20),BetDate,120) as BetDate, BetID, ExternalBetID, Settled, ExternalClientID, AmountToWIn from TblBets (NOLOCK) \r\nWhere ExternalBetID Is Not Null And Channel = 200 {{And $rising_column$ > ?}}\r\n
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
Araitz has it.
Not that I can see 🙂
Here are the settings for the BetID and BetDate columns:
column_name type nullable auto_increment size decimal_digits radix remarks
1 BetID bigint identity NO 19 0 10
3 BetDate datetime NO 23 3 0
L.
is there really an N on the end of your timestamp?