topic DBConnect SQLite not picking proper timestamp in Getting Data In

DBConnect SQLite not picking proper timestamp

stanwin — Tue, 03 Jan 2017 11:50:54 GMT

Hi All

I have an SQLite input setup via DBConnect app .

However the input DBMON tail will just NOT parse proper timestamp..

The ingest date with default time of 00:59:59.999 are always selected
e.g. 04/01/2017 00:59:59.999

I always get below for several config tries..

Ingested data:

04/01/2017 00:59:59.999 Id=2011 Severity=2| ..... |...

The entry in DB table looks like:

2001|2|Invalid XML|2017-01-03 10:44:20|Alrming rate of alarms in alarm |9fd22f2-
g2q-f2f2fe32e2fe|5|f2f22f3-t2c3crr31-c14c14|Nice123|421123|0

Below is set in the Timestamp format option:

yyyy-MM-dd HH:mm:ss.SSS

Timestamp column name is proper ( column name is Timestamp).

Any suggestions please?

Re: DBConnect SQLite not picking proper timestamp

stanwin — Tue, 03 Jan 2017 12:24:18 GMT

DBMON tail query used ( facing the issue):

[dbmon-tail://stanSQLite/SQLight_rowid]
host = chloServer
index = main
interval = 5,11,13,17,20,24,27,33,39,44,49,54,59 * * * *
output.format = kv
output.timestamp = 1
output.timestamp.column = TimeColumn
output.timestamp.format = yyyy-MM-dd HH:mm:ss.SSS
query = SELECT Id  , Severity  , Description  , Timestamp as TimeColumn  , ModelName  , ModelId  , ModelType  , ParentId  , SolutionFileName  , ActionId  , HasAnotherLevel , rowid  FROM Event {{WHERE $rising_column$ > ?}}
source = dbmon-tail://stanSQLite/timestamp_as
sourcetype = dbmon:kv
table = SQLite_tailer
tail.rising.column = rowid

Re: DBConnect SQLite not picking proper timestamp

ppablo — Wed, 04 Jan 2017 00:47:36 GMT

Hi @stanwin

Is this the actual answer to your question, or were you just providing additional context for your issue?

Re: DBConnect SQLite not picking proper timestamp

stanwin — Wed, 04 Jan 2017 10:15:00 GMT

Hey Pablo

yes , sorry that wasn't very clear. I was providing context..

I have tried below solutions :

Rename the column in select query & use that for timestamp column
Altered for several version of Java Date Format . Current one is yyyy-MM-dd HH:mm:ss.SSS
tochar is not available for SQLite , this is one of main workarounds for other DBs

Re: DBConnect SQLite not picking proper timestamp

stanwin — Wed, 04 Jan 2017 14:42:52 GMT

Changing the java date format around just alters the format but resultant times picked are same:

_time _raw
2017-01-04 00:59:59.999 01-01-1970 00:59:59.999 Id=2055 Severity=2 Description=LA
2017-01-04 00:59:59.999 1970-01-01 00:59:59.999 Id=2037 Severity=2 Description="

Re: DBConnect SQLite not picking proper timestamp

sjohnson_splunk — Thu, 05 Jan 2017 14:38:52 GMT

I do not see any milliseconds in the time string from your example. Try dropping the .SSS from the end of the timestamp format.

Re: DBConnect SQLite not picking proper timestamp

adambloom — Thu, 05 Jan 2017 14:39:30 GMT

Is the column a string or in datetime format? DB Connect requires datetime format - it won't take a string or integer timestamp properly (as regular Splunk can). If it's a string, just modify your query to cast to datetime.

Re: DBConnect SQLite not picking proper timestamp

stanwin — Thu, 05 Jan 2017 16:42:26 GMT

Hello sjohnson

Tried this already. it doesn't help. Thanks

Re: DBConnect SQLite not picking proper timestamp

stanwin — Thu, 05 Jan 2017 19:59:30 GMT

Done solved!

SQLite has no date or timestamp data type! The only way I could get it to work is for SPLUNK to automatically select & parse the Timestamp without specifying the Timestamp column or Timestamp format!

There is NO Need to cast to datetime & it will work will the normal text also.. But i preferred to do below ..

SELECT Id , Severity , Description , datetime(timestamp) as timestampCol

Arguably it will mean SPLUNK will parse through the ingest looking for timestamp but I have no option but to leave it that way . Have tried below combinations to NO avail:

select timestamp column & specify timestamp format
use datetime with timestamp column and specify timestamp format
Various alterations of timestamp format with timestamp column

https://www.sqlite.org/datatype3.html
http://www.sqlite.org/lang_datefunc.html

Re: DBConnect SQLite not picking proper timestamp

stanwin — Thu, 05 Jan 2017 20:01:12 GMT

Didnt work that way either, after long researching realised that although TIMESTAMP was the datatype SQLIte will effectively accept anything declared as a datatype!

Re: DBConnect SQLite not picking proper timestamp

stanwin — Tue, 29 Sep 2020 12:19:07 GMT

On a side note..

I tried using DBX version 2 (2.4.0) which does not have th SQLite support by default

But the driver would just NOT show up in list until i added the db types entry to the default db_connection_types.conf instead of local!!

Above accepted answer was tested on DBX version 1. 2.2.