All Apps and Add-ons

Timestamp unreliable from DB onnect source

tonylynch
Explorer

Hi,
I'm currently testing if Splunk (so I'm a Splunk newbie) would work for us for a Big Data project, however I've become stuck just getting splunk to always recognise the timestamp which I'm using (which is the registration date). In some cases it works fine, but in others the date is changed by splunk to a more recent data.
A lot of our data is in MS SQL server 2008 and so I'm using the DB Connect tool. After initial issues, I'm now pointing DB connect at a database view which reformats the date into what I think Splunk wants.
So the example database view (dbo.vw_CONTACT_LIST2) contains 3 records;

REGISTRATIONDATE ROW_ID
2008-03-11T10:00:38.000-0000 2331165
2014-01-06T14:52:20.000-0000 92910125
2013-09-19T17:00:51.000-0000 171817405

I set this up in Spunk and now have the following entry in inputs.conf (added the parse.format manually);

[dbmon-tail://RESPONSYS/RESPONSYS_CONTACT_LIST13]
interval = auto
output.format = kv
output.timestamp = 1
output.timestamp.column = REGISTRATIONDATE
output.timestamp.format = yyyy-MM-dd'T'HH:mm:ss.SSSZ
output.timestamp.parse.format = yyyy-MM-dd'T'HH:mm:ss.SSSZ
table = dbo.vw_CONTACT_LIST2
tail.rising.column = ROW_ID

However the resulting output from splunk seems to get messed up with older dates, so the result is, noting that the timestamp for the first link is 24/02/2014 rather than what I would have expected as in 11/03/2008;

24/02/2014 10:00:38.000 2008-03-11T10:00:38.000+0000 ROW_ID=2331165 host = PQAGAPS2 source = dbmon-tail://RESPONSYS/RESPONSYS_CONTACT_LIST13 sourcetype = dbmon:kv

06/01/2014 14:52:20.000 2014-01-06T14:52:20.000+0000 ROW_ID=92910125 host = PQAGAPS2 source = dbmon-tail://RESPONSYS/RESPONSYS_CONTACT_LIST13 sourcetype = dbmon:kv

19/09/2013 18:00:51.000 2013-09-19T18:00:51.000+0100 ROW_ID=171817405 host = PQAGAPS2 source = dbmon-tail://RESPONSYS/RESPONSYS_CONTACT_LIST13 sourcetype = dbmon:kv

I've now hit a brick wall so I can no longer evaluate the product, so any help would be apprieciated.

Tony

Tags (2)
0 Karma
1 Solution

tonylynch
Explorer

The answer was indeed nothing to do with the format of the dates or an issue with the timestamp, but rather the setting in the props.conf of MAX_DAYS_AGO=2000. The problem dates where all related to records from August 2008, which coincided with the default parameter setting of 2000 days.
After initially changing it, the issue didn't immediately fix, however a restart of the services and everything now appears to be working.

View solution in original post

tonylynch
Explorer

The answer was indeed nothing to do with the format of the dates or an issue with the timestamp, but rather the setting in the props.conf of MAX_DAYS_AGO=2000. The problem dates where all related to records from August 2008, which coincided with the default parameter setting of 2000 days.
After initially changing it, the issue didn't immediately fix, however a restart of the services and everything now appears to be working.

View solution in original post

tonylynch
Explorer

I think this might have something to do with the setting in the props.conf file MAX_DAYS_AGO=2000, however if I change this value to 4000 it doesn't seem to fix the issue.

0 Karma

tonylynch
Explorer

I think the issue is something to do with dates with dates in August 2008 and before. Not sure why this would be the case, but this seems to be when the date changes to the current date. So I think this might be the root cause. Can anyone shed any light on this and how to workaround this issue?

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!