DB Connect MS SQL Datetime

[devicenul1]

Splunk not reading my datetime value correctly:

select top 1 convert(datetime,posting_date) as PostedDate

Result:
PostedDate: 2005-12-08 08:26:00.000

Following the steps in the documentation this should be all I need to do, but it's not working. Instead Splunk is indexing the event based on the date / time of the Splunk server.

I also tried following the workaround suggested in the documentation:
output.timestamp.parse.format = yyyy-mm-dd hh:MM:ss.SSS

and it still doesn't work, just not sure what I'm missing.

Updated inputs.conf

[dbmon-tail://RO_DB/PostedOrders]
host = DBSRVR
index = DBIndex
interval = 2m
output.format = kv
output.timestamp = 1
output.timestamp.column = por_POSTING_DATE
query = SELECT por_POSTING_DATE,por_ORD_KEY,por_POSTING_DATE as PostedDate from table {{WHERE $rising_column$ > ?}}
sourcetype = PostedOrders
tail.rising.column = por_ORD_KEY
table = PostedOrders
disabled = 0
output.timestamp.format = yyyy-mm-dd hh:MM:ss

Updated Props.conf

[PostedOrders]
MAX_DAYS_AGO = 5000
TIME_FORMAT=%s

[linu1988]

Hello Devicenul,
There is no need of having the convert function. Is the field in datetime format in DB? If yes DB Connect will automatically recognize that and assign it to your events.

Sample configuration which works for me.

Updated Config:

inputs.conf

[dbmon-tail://test/taily]
index = main
interval = 30
output.format = csv
output.timestamp = 0
output.timestamp.column = posting_date
output.timestamp.format = %d/%m/%Y %H:%M:%S
query = select top 1 posting_date,posting_date as PostedDate {{WHERE $rising_column$ > ?}}
sourcetype = dummy
table = dummy
tail.rising.column = posting_date

Props.conf

[dummy]
MAX_DAYS_AGO = 5000
TIME_FORMAT=%s

Splunk will consume the posting_date field to get the eventtime.

Note: if any rising column value has already been recorded then go to
Splunk\var\lib\splunk\persistentstorage\dbx\d6db0e2901cbef084bc0f4c01134d4f6 kind of dir

Thanks

[devicenul1]

No errors in dbx.log.

I've just worked around this by keeping the data in the DB And doing a DBQuery with a conversion on the epoch time for the $earliest$ and $latest$ time range values. Again, thank you for your help with this problem but I'm considering this closed at this point and just keeping our data in the DB.

[linu1988]

yeah that is correct not a problem. check the dbx.log file what it says , any error recognizing the time field. Could you contact me on my mail id on profile, would take a look on team viewer. It's really weird it still doesn't work for you! 😞

[devicenul1]

Updated my props.conf above ... unfortunately it's still doing the same thing. Between each test I am doing

sourcetype=PostedOrders | delete

so I can ensure they are only new events we are testing against. Only other thing I can think of is i'm updating the props.conf in the $SplunkHome$\etc\apps\dbx\default folder, is that the correct one?

[linu1988]

your configuration is wrong. the source name is wrongly mentioned. just keep the below in your props.conf

[PostedOrders]
MAX_DAYS_AGO = 5000
TIME_FORMAT=%s

check for the oldest event after you index the records.

sourcetype=PostedOrders|stats last(_time) as last_time by sourcetype|convert ctime(last_time)

if you see 2005 timestamp that's it!!! All the best

[devicenul1]

I updated my props.conf (See above) and restarted splunk, same thing. It's showing the date / time from the output.timestamp in the event as "2014-05-20 11:00:48" but when looking at the DB it's actually returning "2005-12-21 08:52:00"

[linu1988]

por_POSTING_DATE if it is your datetime field it should be in epoch time format. You should set it as TIME_FORMAT=%s. and add this as well in props.conf MAX_DAYS_AGO = 5000. it should give you the exact date as your DB record. Hope it finally does the trick.

[devicenul1]

Yeah, I updated my original post to exactly how my inputs.conf and props.conf are reading right now. So after a new rebuild and setting it all back up again (as above), it is still pulling the dates in as Today but because I'm outputting the time to the event I see that it's saying the por_POSTING_DATE is today but that is not true. A quick select from the DB is showing the date as 2005-12-16 11:11:00 so I'm not sure why it's different now. Maybe my props.conf?

[linu1988]

Did you set the props.conf in your app updated in your post? that is really required. Even after that it doesn't work i have to officially tell sorry and wait for some Splunk person to assist you.

[devicenul1]

Well I uninstalled splunk and removed all files and reinstalled clean ... added the inputs.conf lines I have updated above ... worked once to import the first 20 items. But still with the date being indexed incorrectly. I added a output.timestamp.parse.format line and it blew up again. I'm officially giving up on the DB Connect app (I've been working on this since Monday).

Thank you for your help linu1988 it was greatly appreciated, I up-voted your post but can't award the answer as I couldn't make it work properly.

[linu1988]

check in dbx.log for any kind of error. It works great for many so shouldn't be disheartened with small failures

[devicenul1]

well, this just got worse ... after looking at your most recent modifications and trying to update my inputs.conf DB Connect blew up. It will not run at all anymore and give me the error

DatabaseMonitorValidator returned status code 47 see splunkd.log for more details

but splunkd.log doesn't contain anymore details ... This happened to me on Wednesday too. IMO this DB Connect app is extremely untrustworthy.

[linu1988]

i have done some modification.

if possible follow my step to clear the counter to monitor from starting if they come correctly.

output.timestamp.format is not that important so ignore it for now

[devicenul1]

No errors in dbx.log unfortanetly. I added this to the dbx\props.conf file

[source::dbmon-tail://RO_DB/PostedOrders]
TIME_FORMAT = %Y%m%d %H%M%S

I'm not sure if this is correct, from the little reading I've done about props.conf I think it's correct, but when running the tail it still doesn't correctly place the event on the right day. Still just reads it all as today. I updated my original posted with the dbx\local\inputs.conf file I currently have.

[linu1988]

got the real problem where splunk doesn't check the old date so need to set a props.conf. Try my updated answer

[linu1988]

do you get any error in dbx.log? does it show you any error like ERROR PARSING TIMESTAMP? OR anything?

[devicenul1]

Ugh, I've spent like a day trying to fix this and I can't get anywhere with it. I do have the {{ $rising_column$ > ?}} stuff in my query and that is working as expected. It's just this datetime stuff I can't make work.

DB Connect 1.1.4 here

[linu1988]

forgot to mention one simple thingy

select top 1 convert(datetime,posting_date) as PostedDate {{ ... $rising_column$ > ?}}

for dbtail. I have just done with some tests with you kind of sample data splunk recognized everything perfectly. Using DB connect 1.1.3

[linu1988]

if you are getting the data and the time.format.parse doesn't work try setting the source in a props.conf file for time recognition.

[devicenul1]

It's still not indexing the correct datetime, keeps saying everything was today (but it is using the correct time). I noticed you said to "dump your data into some default index" ... so I changed the index to the default Splunk index (before I had created an index for this data) and I'm still experiencing the problem, I'm also not using DB-Mon Dump, I'm using DB-Mon Tail but the same rules for datetime field should still apply correct?