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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...