All Apps and Add-ons

How to define the Datetime Format on Splunk DB Connect?

edrivera3
Builder

I'm creating inputs for multiple SQLite tables. I already made 8 different inputs and all of them worked as expected.
I suspect that the problem is related to the Datatime Format which is the only difference between them.

I have a very simple query:  SELECT * FROM stepHistory WHERE id > ? ORDER by id asc;
Input  Type:  Rising
Rising Column: id
Checkpoint Value:  0
Column: timestamp
Datetime Format: EEE MMM d HH:mm:ss yyyy

Example of timestamp:
Thu Mar 8 02:05:00 2018
Wed Feb 28 20:16:04 2018

I'm able to do "Execute SQL" correctly and I see that it is retrieving the correct data.

Max Rows to Retrieve: 0
Fetch Size: 300
Execution Frequence: 17 * * * *

Is there something that I'm missing?

I looked at the index, it is empty. I did a search just with the index name and I got the following:

2018-04-02 09:17:00.043 -0700  [QuartzScheduler_Worker-30] INFO  org.easybatch.core.job.BatchJob - Job 'atedb_stephistory' finished with status: FAILED

2018-04-02 09:17:00.043 -0700 INFO  c.s.dbx.server.task.listeners.JobMetricsListener - action=collect_job_metrics connection=atedb jdbc_url=null record_read_success_count=111 db_read_time=33 record_read_error_count=1 hec_record_process_time=8 format_hec_success_count=111 status=FAILED input_name=atedb_stephistory batch_size=1000 error_threshold=N/A is_jmx_monitoring=false start_time=2018-04-02_09:17:00 end_time=2018-04-02_09:17:00 duration=43 read_count=111 write_count=0 filtered_count=0 error_count=0

2018-04-02 09:17:00.006 -0700  [QuartzScheduler_Worker-30] INFO  org.easybatch.core.job.BatchJob - Job 'atedb_stephistory' started
2018-04-02 09:17:00.005 -0700  [QuartzScheduler_Worker-30] INFO  com.splunk.dbx.connector.logger.AuditLogger - operation=dbinput connection_name=atedb stanza_name=atedb_stephistory state=success sql='SELECT *
FROM stepHistory
WHERE id > ?
ORDER BY  id asc; '

2018-04-02 09:17:00.001 -0700  [QuartzScheduler_Worker-30] INFO  c.s.d.s.dbinput.recordreader.DbInputRecordReader - action=db_input_record_reader_is_opened input_task="atedb_stephistory" query=SELECT *
FROM stepHistory
WHERE id > ?
ORDER BY  id asc; 

2018-04-02 09:17:00.000 -0700  [QuartzScheduler_Worker-30] INFO  org.easybatch.core.job.BatchJob - Job 'atedb_stephistory' starting

Edit:
After looking more closely at the events, there is an error on row 112:

"record_read_success_count=111".
As I expected, the issue is related to the timestamp. Note that there are two spaces between "Mar" and "7". I'm not sure how to manage this.

111 | Thu Feb 15 17:49:21 2018
112 | Wed Mar  7 20:11:45 2018

I'm changing the title of the question from "Unable to make a DB connect input" to "How to define the Datetime Format on Splunk DB Connect?"

0 Karma

mpipaliya_splun
Splunk Employee
Splunk Employee

There seem to be issue with date/time format. See if Date time Format: %a %b %e %H:%M:%S %Y for Wed Mar 7 20:11:45 2018 works.

If given timestamp does not solve the issue, please share content of inputs.conf and props.conf to analyze further.

For more details on date/time format in Splunk, you may refer to docs on Commontimeformatvariables:


Mayur @ Splunk | GSS FDSE | Personal: https://mayur.pipaliya.com/

If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...