Timestamp problem with DB Connect

[jedatt01]

I have a MSSQL Datasource and having trouble parsing the timestamps (ProcessWorkStart field) correctly. For some reason it is parsing all events with the exact same timestamp (1969-12-31 18:59:59.999) no matter how I am configuring the inputs.conf. Please help! See below a sample event and my inputs.conf

SAMPLE EVENT
1969-12-31 18:59:59.999 CoreWorkerProcessResultId=37114016 CoreProcessId=2 CoreProcess="xxxxxxx" CoreWorkerId=1 CoreWorker=xxxxxxxx GetWorkStart="2014-09-14 03:15:48.7170000" GetWorkMs=54 TotalWorkCount=0 GetTotalWorkCountMs=1 HasWork=false MessageCount=0 ProcessWorkMs=0

Inputs.conf
[dbmon-tail://SAMMY/SAMMY_Oper_Logs]
index = emme
interval = 60
output.format = kv
output.timestamp = 1
output.timestamp.column = ProcessWorkStart
output.timestamp.format = yyyy-MM-dd HH:mm:ss.SSS
query = select [CoreWorkerProcessResultId]\r\n ,cwpr.[CoreProcessId]\r\n ,cp.Description as [CoreProcess]\r\n ,cwpr.[CoreWorkerId]\r\n ,cw.Description as [CoreWorker]\r\n ,[GetWorkStart]\r\n ,[GetWorkMs]\r\n ,[TotalWorkCount]\r\n ,[GetTotalWorkCountMs]\r\n ,[HasWork]\r\n ,[MessageCount]\r\n ,[ProcessWorkStart]\r\n ,[ProcessWorkMs]\r\n from CoreWorkerProcessResult cwpr \r\n inner join CoreProcess cp on cp.CoreProcessId = cwpr.CoreProcessId\r\n inner join CoreWorker cw on cw.CoreWorkerId = cwpr.CoreWorkerId\r\n where cwpr.CoreWorkerProcessResultId > 37000000 {{AND $rising_column$}}
sourcetype = emme_core_stat
tail.rising.column = CoreWorkerProcessResultId
table = EMME_Oper_Logs
disabled = 1

[jedatt01 · Accepted solution]

I figured it out. I ended up casting the appropriate field to datetime type then removed the output.timestamp.format = "yyyy-MM-dd HH:mm:ss.SSS" from props.conf and it worked! Thanks everyone for helping me get there!

[theouhuios]

Use the java timestamp format. Not the Splunk Timestamp format in output.timestamp.format . DBConnect

Check the links below. 1st one is the Splunks DOC and the line mentioned is what Splunk says on what timestamp to use.

http://docs.splunk.com/Documentation/DBX/1.1.4/DeployDBX/Configuredatabasemonitoring

4. Specify the Timestamp format. This is a Java SimpleDateFormat pattern. The default format is configurable during setup. 

This link talks about the timeformat in java.

http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Hope this helps.
theou

[jedatt01]

I've followed the java format, but still something is missing here, I can't understand why it won't read the timestamp i've asked it to

This is still not working. I'm not sure whats the problem is.

Inputs.conf
[dbmon-tail://SAMMY/SAMMY_Oper_Logs]
index = emme
interval = 60
output.format = kv
output.timestamp = 1
output.timestamp.column = ProcessWorkStart
output.timestamp.format = "yyyy-MM-dd HH:mm:ss.SSS"
query = select [CoreWorkerProcessResultId]rn ,cwpr.[CoreProcessId]rn ,cp.Description as [CoreProcess]rn ,cwpr.[CoreWorkerId]rn ,cw.Description as [CoreWorker]rn ,[GetWorkStart]rn ,[GetWorkMs]rn ,[TotalWorkCount]rn ,[GetTotalWorkCountMs]rn ,[HasWork]rn ,[MessageCount]rn ,[ProcessWorkStart]rn ,[ProcessWorkMs]rn from CoreWorkerProcessResult cwpr rn inner join CoreProcess cp on cp.CoreProcessId = cwpr.CoreProcessIdrn inner join CoreWorker cw on cw.CoreWorkerId = cwpr.CoreWorkerIdrn where cwpr.CoreWorkerProcessResultId > 37000000 {{AND $rising_column$}}
sourcetype = emme_core_stat
tail.rising.column = CoreWorkerProcessResultId
table = EMME_Oper_Logs

[theouhuios]

Does this query work when you try it in Db Query? Remove the tailing part and see if this works. If it does, then do a Cast on the time. After that change the where cwpr.CoreWorkerProcessResultId > 37000000 {{AND $rising_column$}} to {{ where cwpr.CoreWorkerProcessResultId > 37000000 AND $rising_column$ > ?}}

[antlefebvre]

You have a space in your time stamp format. Try throwing quotes around the string.

[jedatt01]

This is still not working. I'm not sure whats the problem is.

Inputs.conf
[dbmon-tail://SAMMY/SAMMY_Oper_Logs]
index = emme
interval = 60
output.format = kv
output.timestamp = 1
output.timestamp.column = ProcessWorkStart
output.timestamp.format = "yyyy-MM-dd HH:mm:ss.SSS"
query = select [CoreWorkerProcessResultId]rn ,cwpr.[CoreProcessId]rn ,cp.Description as [CoreProcess]rn ,cwpr.[CoreWorkerId]rn ,cw.Description as [CoreWorker]rn ,[GetWorkStart]rn ,[GetWorkMs]rn ,[TotalWorkCount]rn ,[GetTotalWorkCountMs]rn ,[HasWork]rn ,[MessageCount]rn ,[ProcessWorkStart]rn ,[ProcessWorkMs]rn from CoreWorkerProcessResult cwpr rn inner join CoreProcess cp on cp.CoreProcessId = cwpr.CoreProcessIdrn inner join CoreWorker cw on cw.CoreWorkerId = cwpr.CoreWorkerIdrn where cwpr.CoreWorkerProcessResultId > 37000000 {{AND $rising_column$}}
sourcetype = emme_core_stat
tail.rising.column = CoreWorkerProcessResultId
table = EMME_Oper_Logs

[antlefebvre]

Splunk may be reading the time stamp as a string. Try this solution from the link theou provided :

For example, when Splunk indexes your data, it looks for a timestamp of the DATETIME datatype. If your timestamp is a string value (such as VARCHAR, NVARCHAR, etc.), you can try to convert the timestamp to the correct datatype using a custom SQL statement with CAST, CONVERT, or TO_TIMESTAMP functions.

[jedatt01]

The datatype for this field is datetime2. Could that be the problem? I tried to cast the field to datetime but it's still not working correctly