Getting Data In

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

lukeh
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

araitz
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

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

lukeh
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

jcoates_splunk
Splunk Employee
Splunk Employee

Araitz has it.

0 Karma

lukeh
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

jcoates_splunk
Splunk Employee
Splunk Employee

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...