db connect event time mismatch

[madhav_dholakia]

Hello,

I have a query regarding getting data in using DB Connect App. I am using Splunk cloud instance and DB Connect is installed on IDM.

On DB Connect, there is a connection created for MySQL DB. Possibly because of some time zone issues, I am getting the data from MySQL DB to DB Connect with a delay of an hour.

I have tried changing timezone under jdbc connection screen and also tried adding "useLegacyDatetimeCode=false" in jdbc path but that has not helped.

Can someone please suggest what else I can check here?

Thank you.

[misterduke]

Hello,

some questions about your issue:

- do you query the data from your database via batch or rising column? 

- is there a timestamp included in your data string ? (field with timestamp)

- did you check the Timezone in your dbconnect database connection ? 

- does your splunk server have the correct time/timezone to begin with? 

maybe some of those questions give you a possible hint in the right direction. otherwise, I'm happy to help ofc

[madhav_dholakia]

Hi @misterduke , apologies for the delayed response. Please below details, can you please suggest in case any changes required here.

I am querying the data from mysql database via rising column.

There is a column called "Last Update" which is a timestamp and that is set as a rising column.

Timezone in my dbconnect database connection is Etc/GMT: +00:00

My user on splunk server has the timezone as GMT/London.

Thank you.

[misterduke]

So usually with dbconnect you can set the used timestamp at "indexing time" or a specific field. do you have any other timestamp field which shows the creation of the dataset? if so, you can use this field as your timestamp and the "last update" as your rising column granted it changes everytime when something is changed in your dataset.

if you already used another field OR the indexing time option in dbconnect and get a delay of exactly 1 hour in your timestamp then it's probably a timezone issue:

- Splunk is per default indexing data in UTC. you can customize the timezone for dbconnect in dbconnections.conf (Edit connection > Settings ) 

- The Server in which Splunk resides has its own timezone configuration. when you use "indexing time" option you might need to tweak this configuration

[madhav_dholakia]

Hi @misterduke - thank you for your suggestions and sorry for the very delayed reply.

MySQL DB Server Timezone is UTC

I have changed Timezone under DBConnect - Connection - Configuration as "Etc/UTC: +00:00" but I can see negative latency here, i.e.,

_indextime MINUS _time gives me a negative value

Can you please suggest what else I can check to get this fixed.

Thank you.

[misterduke]

Hello,

so basically the time of your event timestamp is "behind" the timestamp your indexer sets for the indexed event. therefore the events aren't using the given timestamp in the event. in the props.conf you need to define the sourcetype and set the following parameters to match the event timestamp:

TIME_PREFIX

MAX_TIMESTAMP_LOOKAHEAD

TIME FORMAT

as seen in : https://docs.splunk.com/Documentation/Splunk/8.0.5/Data/Configuretimestamprecognition

With those you tell splunk to use the timestamp that is in the event itself rather than it's own time and therefore you eliminate the possibility of latency between _time and _indextime . hope that'll do