Getting Data In

Timestamp problem with DB Connect

jedatt01
Builder

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

0 Karma
1 Solution

jedatt01
Builder

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!

View solution in original post

jedatt01
Builder

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
Motivator

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
Builder

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

0 Karma

theouhuios
Motivator

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$ > ?}}

0 Karma

antlefebvre
Communicator

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

0 Karma

jedatt01
Builder

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

0 Karma

antlefebvre
Communicator

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.

0 Karma

jedatt01
Builder

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

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