Getting Data In

DBConnect SQLite not picking proper timestamp

Contributor

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?

0 Karma
1 Solution

Contributor

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

View solution in original post

Contributor

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.

0 Karma

Contributor

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

View solution in original post

Path Finder

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.

0 Karma

Contributor

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

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

Contributor

Hello sjohnson

Tried this already. it doesn't help. Thanks

0 Karma

Contributor

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="

0 Karma

Contributor

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
0 Karma

Community Manager
Community Manager

Hi @stanwin

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

0 Karma

Contributor

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
0 Karma