All Apps and Add-ons

DB Connect 2 millisecond timestamp fields Frankenstien _time

phoenixdigital
Builder

Hi All,

Seeing some very strange results from a DB input.

[mi_input://SVR-DEV]
connection = SVR-DEV
index = server_application
interval = 43200
max_rows = 10000
mode = tail
output_timestamp_format = YYYY-MM-dd HH:mm:ss
query = Select * from log.log_event_logs
source = dbx2:svr-dev
sourcetype = db:svr
tail_follow_only = 1
tail_rising_column_name = EVENT_LOG_ID
tail_rising_column_number = 4
ui_query_catalog = NULL
ui_query_mode = advanced
ui_query_schema = undefined
ui_query_table = undefined
input_timestamp_column_name = EVENT_TIMESTAMP
input_timestamp_column_number = 11
tail_rising_column_checkpoint_value = 215663
disabled = 1

Now for the interesting part. DB Connect 2 performs the following

"2015-12-30 08:50:10" EVENT_LOG_ID=157931, EVENT_TIMESTAMP=1419889810000, EVENT_TYPE="INVALID_ROLE", EVENT_SEVERITY_CODE="ERROR", EVENT_TEXT="Message authorization failed for id:", EVENT_DETAIL_DATA="A............

Note EVENT_TIMESTAMP=1419889810000

  • which is a millisecond timestamp of 1419889810.000
  • which equates to Mon, 29 Dec 2014 21:50:10 GMT
  • adjusting for timezone +10 hours = Mon, 30 Dec 2014 8:50:10

So the first issue here is DB Connect 2 added a year to the date!

The second issue is _time for this event is 24/11/2015 08:50:10.000!

I can see the time 8:50:10 is right but where did it get 24/11/2015 from???

Further investigation into the event in Splunk 24/11/2015 was the index time.

So does anyone know to resolve this

  1. The timestamp is getting broken in ingestion of the data (adding one year)
  2. _time is a Frankenstein combination of index_time (date) and the timestamp (time)

To be clear no changes have been made to MAX_DAYS_AGO (default 2000 days) and MAX_DAYS_HENCE (default 2 days) so these should be fine with defaults.

The only other thing I can think of would be a props.conf entry for the TIME_FORMAT to specifically tell Splunk to match the output_timestamp_format in the DB input definition. But it is a pretty standard format I would have thought Splunk would recognise it in an instant.

Tags (1)
0 Karma

evinasco
Communicator

Dude

please try yyyy instead of YYYY in your inputs

regards

0 Karma

Rjaramillom
Engager

Try yyyy instead of YYYY. The fomat YYYY is weakyear, which can give weird results

0 Karma

Richfez
SplunkTrust
SplunkTrust

Which timestamp do you want to use?

It might help to double-check the "timestamp" section of the Troubleshooting section of DBx2.

0 Karma

phoenixdigital
Builder

I thought this line handled setting the timestamp as that column for my input?

input_timestamp_column_name = EVENT_TIMESTAMP

It should be noted that the config I showed above was the result from using the web GUI. I did not try to tweak anything.

It should also be noted the old DBX 1 app handled a millisecond timestamp just fine. So unsure why DB Connect 2 is breaking so spectacularly?

Looking at my old working DB Connect 1 inputs.conf it was

[dbmon-tail://SVR-DEV/SVR-DEV]
host = dev-oracle
index = application
interval = auto
output.format = mkv
output.timestamp = 1
output.timestamp.column = EVENT_TIMESTAMP
query = Select * from log.log_event_logs {{WHERE $rising_column$ > ?}}
sourcetype = db:svr
table = SVR-DEV
tail.rising.column = EVENT_LOG_ID
disabled = 0
0 Karma

phoenixdigital
Builder

Also that troubleshooting section you linked to is peppered with DBX 1 config commands. In particular "If your timestamp is not of type datetime/timestamp"

The documentation is wrong or the web GUI config creation when entering timestamp columns is wrong. Either way that is pretty poor form for a troubleshooting page having a red herring in there like that.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...