Getting Data In

Why is my DBconnect input behind so far?

markmcd
Path Finder

I have set up DB Connect on my Splunk 4.3 installation to provide an input that tails log data from a table. I didn't set up any custom SQL, I have set up the rising column to be "Id" and I have set up the timestamp column seemingly correctly.

The problem is that the newest data in Splunk appears to be about 8 hours behind the database table. Splunk is certainly indexing the data, as the summary page shows the count incrementing every second or so. The data are correct, in that the timestamps shown reflect what is in the database but they're stuck at about 8 hours behind (i.e. at 9am I can only see logs from about 1am).

I'm not sure if this is a red herring or not, but my timezone is UTC+8. The 8 hour difference could just be a coincidence but I can't be certain. The log timestamps are also in UTC+8.

When I run Splunk query like so:

sourcetype="dblogs" |stats max(Id) as maxid

And a similar SQL query:

SELECT Max(Id) FROM Log;

The database is significantly ahead of Splunk (by about 22k - which is about right).

Is indexing affected by the timestamp field & potential timezone offsets? Is there any other setting I can tweak to get it up to speed?

EDIT: Some new information that will hopefully bump this a little:

When I check the 'state.xml' file for this input, I get the following (except in real XML, not []).

[list]
  [value key="latest.Id"]
    [value class="int"]61293050[/value]
  [/value]
[/list]

This matches up with the database's max ID when I run an SQL query directly. However when I run a stats max(Id) query in Splunk I get something from 8 hours ago:

+----------+---------------------+
| Id       | CreatedOn           |
+----------+---------------------+
| 61285887 | 2013-05-31 01:13:18 |
| 61293050 | 2013-05-31 09:17:24 |
+----------+---------------------+

If it helps, my Splunk server is running in UTC timezone, as is the MySQL database server. The data is logged in UTC+8.

0 Karma
1 Solution

markmcd
Path Finder

The solution to this was to add a 'TZ = +0800' to a new stanza for the sourcetype in props.conf. No re-index required, the data just became searchable.

View solution in original post

0 Karma

markmcd
Path Finder

The solution to this was to add a 'TZ = +0800' to a new stanza for the sourcetype in props.conf. No re-index required, the data just became searchable.

0 Karma

markmcd
Path Finder

Actually it looks like Splunk has indexed the data, but I can't search for it as it's in the future. I suspect I'll need to blow it away & re-index in UTC time format. Will I need to do that, or can I mark the timezone in Splunk somehow?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...