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!

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