Archive

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

Contributor

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 🙂

0 Karma
1 Solution

Splunk Employee
Splunk Employee

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...

View solution in original post

Splunk Employee
Splunk Employee

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...

View solution in original post

Contributor

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
0 Karma

Splunk Employee
Splunk Employee

Araitz has it.

0 Karma

Contributor

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.

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!