Getting Data In

DBConnect SQLite not picking proper timestamp

stanwin
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

stanwin
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

stanwin
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

stanwin
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

adambloom
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

stanwin
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

sjohnson_splunk
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

stanwin
Contributor

Hello sjohnson

Tried this already. it doesn't help. Thanks

0 Karma

stanwin
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

stanwin
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

ppablo
Retired

Hi @stanwin

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

0 Karma

stanwin
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
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 ...