All Apps and Add-ons

DB Connect 3.1.1 - Unable to ingest SQLite data when using a rising column and a column as a timestamp field.

franzferd
Engager

So I am trying to ingest data from a SQLite database using DB Connect 3.1.1. I would like to use a rising input. When I choose my own timestamp column, the data will not ingest. When I use the system timestamp, the data will ingest.

I have narrowed my test down to two fields, and am still unable to ingest. Below is my statement:

SELECT id, date
FROM results

WHERE id > ?
ORDER BY id ASC

The "id" column is my rising column.
The "date" is the column I would like to use as my timestamp.
The "date" column data is in epoch format.
I have tried converting epoch to standard time, and am having the same result.
DB Connect recognizes both columns as integers.
My DB Connect is on a heavy forwarder and the data is forwarding other SQLite data (when a custom timestamp is not used).

Any thoughts?

0 Karma
1 Solution

franzferd
Engager

I figured out the problem.

When using a custom timestamp, I was unable to use the field as epoch. I ended up with a select statement that used strftime to change the epoch time to a more legible format:

SELECT id,
    strftime('%m/%d/%Y %H:%M:%S', datetime(date, 'unixepoch'), "localtime") AS timestamp
FROM results
WHERE id > ?
ORDER BY id ASC

I then used this Datetime format: MM/dd/yyyy HH:mm:ss

From here, my SQLite data is propagating correctly and using the date field I want as the _time.

View solution in original post

0 Karma

franzferd
Engager

I figured out the problem.

When using a custom timestamp, I was unable to use the field as epoch. I ended up with a select statement that used strftime to change the epoch time to a more legible format:

SELECT id,
    strftime('%m/%d/%Y %H:%M:%S', datetime(date, 'unixepoch'), "localtime") AS timestamp
FROM results
WHERE id > ?
ORDER BY id ASC

I then used this Datetime format: MM/dd/yyyy HH:mm:ss

From here, my SQLite data is propagating correctly and using the date field I want as the _time.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...