DB Connect Error instantiating output format kv: java.lang.IllegalArgumentException: Illegal pattern character 'N'

[lukeh]

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 🙂

[araitz · Accepted solution]

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:

http://answers.splunk.com/answers/95609/splunk-dbconnect-app-not-reading-timestamp-column-value-corr...

[lukeh]

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

[jcoates_splunk]

Araitz has it.

[lukeh]

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.

[jcoates_splunk]

is there really an N on the end of your timestamp?