How configure Splunk to get the correct timestamp from SQL data files?

[danielvalle]

Hi,

I am using Splunk to get data files from SQL queries. One of the fields in the document corresponds to the date.

I can assign the format required:

01/25/2014
01/25/2014
2014/01/25
etc. ..

Right now, I'm testing with a file with the following format:

Timestamp	CUENTA	IP
11/22/00	reportes	192.168.60.10
02/15/00	admin	192.168.1.24
01/27/00	publico	192.168.1.82
01/27/00	publico	192.168.1.82
01/27/00	publico	192.168.1.82

but I can not get Splunk correctly recognize the timestamp field and when I preview the result before the load is as follows:

Timestamp Timestamp CUENTA IP
1 9/9/01 1:48:19.000 PM -----------+--------------+----------------- N/A N/A
2 9/9/01 1:48:19.000 PM 11/22/00 reportes 192.168.60.10
3 9/9/01 1:48:19.000 PM 02/15/00 admin 192.168.1.24
4 9/9/01 1:48:19.000 PM 01/27/00 publico 192.168.1.82
5 9/9/01 1:48:19.000 PM 01/27/00 publico 192.168.1.82
6 9/9/01 1:48:19.000 PM 01/27/00 publico 192.168.1.82
7 9/9/01 1:48:19.000 PM 01/27/00 admin 192.168.1.82
8 9/9/01 1:48:19.000 PM 01/27/00 admin 192.168.1.82
9 9/9/01 1:48:19.000 PM 01/27/00 cat 192.168.1.82
10 9/9/01 1:48:19.000 PM 01/27/00 admin 192.168.1.82
11 9/9/01 1:48:19.000 PM 02/09/00 admin 127.0.0.1

Do I have to configure the data file in some special way? Should I somehow configure Splunk to recognize the value of the timestamp field?

Thank you very much for your help,

[kml_uvce · Accepted solution]

use this in props.conf
MAX_DAYS_AGO=10951
NO_BINARY_CHECK=1
SHOULD_LINEMERGE=false
TIME_FORMAT=%m/%d/%y
TIME_PREFIX=^

or write in advanced mode(props.conf) in text (web) when you are doing preview

[danielvalle]

Thank you! It's worked! 🙂