topic Re: DB Connect MS SQL Datetime in Splunk Search

DB Connect MS SQL Datetime

devicenul1 — Thu, 15 May 2014 17:00:36 GMT

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

Re: DB Connect MS SQL Datetime

linu1988 — Thu, 15 May 2014 17:07:31 GMT

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

Re: DB Connect MS SQL Datetime

devicenul1 — Thu, 15 May 2014 18:45:49 GMT

the posting_date column is in the smalldatetime format without the convert() the data returns a bit differently(result: 2008-05-09 10:30:00) so should my inputs.conf look like this:

output.timestamp=1
output.timestamp.column = PostedDate
output.timestamp.format = yyyy-mm-dd hh:MM:ss
query= select top 10 posting_date as PostedDate from table

Is the timestamp.format of dd/mm/yyy hh:MM:ss in your config because splunk wants it that way? I see you also have an output.timestamp of =0 so is the .format line even necessary?

Re: DB Connect MS SQL Datetime

linu1988 — Thu, 15 May 2014 19:06:27 GMT

no it is not necessary as you rightly said , output.timestamp = 1 then you need the format else all the config is fine and the output.timestamp doesn't affect the input. Just try dump your data into some default index to see if it works.

More than that just check in dbx.log what exactly is happening... Please come back if you see any issue 🙂

Re: DB Connect MS SQL Datetime

devicenul1 — Thu, 15 May 2014 19:14:26 GMT

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?

Re: DB Connect MS SQL Datetime

linu1988 — Thu, 15 May 2014 19:39:58 GMT

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.

Re: DB Connect MS SQL Datetime

linu1988 — Thu, 15 May 2014 19:56:30 GMT

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

Re: DB Connect MS SQL Datetime

devicenul1 — Thu, 15 May 2014 20:13:58 GMT

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

Re: DB Connect MS SQL Datetime

linu1988 — Thu, 15 May 2014 21:01:51 GMT

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

Re: DB Connect MS SQL Datetime

linu1988 — Thu, 15 May 2014 21:28:15 GMT

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

Re: DB Connect MS SQL Datetime

devicenul1 — Fri, 16 May 2014 12:22:59 GMT

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.

Re: DB Connect MS SQL Datetime

linu1988 — Fri, 16 May 2014 15:46:56 GMT

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

Re: DB Connect MS SQL Datetime

devicenul1 — Fri, 16 May 2014 18:40:37 GMT

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.

Re: DB Connect MS SQL Datetime

linu1988 — Fri, 16 May 2014 18:51:19 GMT

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

Re: DB Connect MS SQL Datetime

devicenul1 — Fri, 16 May 2014 19:26:30 GMT

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.

Re: DB Connect MS SQL Datetime

linu1988 — Fri, 16 May 2014 19:38:56 GMT

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.

Re: DB Connect MS SQL Datetime

devicenul1 — Mon, 28 Sep 2020 16:39:42 GMT

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?

Re: DB Connect MS SQL Datetime

linu1988 — Mon, 28 Sep 2020 16:39:45 GMT

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.

Re: DB Connect MS SQL Datetime

devicenul1 — Tue, 20 May 2014 15:03:06 GMT

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"

Re: DB Connect MS SQL Datetime

linu1988 — Mon, 28 Sep 2020 16:40:29 GMT

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