Timestamp issues with DBX 1.x and how to fix it

[anhtrantech]

Hello,
I have a big issue with the timestamp that i hope you can help.
I used the DBX and MS SQL. We can't use trailing column due to our application. I dump the whole DB every 30s. So the timestamp is assigned by the indexing time which messed up entire Time picker , the 1h, 4h, 1day etc doesn't work properly since it used the indexing time.

our event in the DB is like this

2015-05-22T13:09:25.455 JobID=6255 UserName=Jd123 JobStatus=Finished JobStartTime=1432310127.857 TaskStartTime=1432310144.080 TaskFinishTime=1432310182.503 JobFinishTime=1432310206.213

We wanted to query based on the JobFinishTime such as 1h , 4h, 1day etc.

The current query is

index="jobinfo" JobStatus=Finished | stats count,values(Machine) as Machine by UserName ,JobStatus, JobStartTime | table UserName JobID Machine JobStatus JobStartTime JobFinishTime | convert ctime(JobStartTime) ctime(JobFinishTime) | sort  -JobID

How can we format FinishedTime to compare with the current time to show the last 1h, 4h, 24h etc ? Or better yet, how to fix the how timestamp issue with DBX ,

Thank you very much for your help.enter code here

[dfronck]

Can't you just set the "Timestamp column" to JobFinishTime in the Outputs section of the "Database Inputs" page?

[dflodstrom]

I think you'll be happier if you correctly determine the timestamp at index time. You can do this in props.conf using this as a guide: http://docs.splunk.com/Documentation/Splunk/6.2.3/Data/Configuretimestamprecognition