Getting Data In

How to configure Splunk to recognize the timestamp for events is in several columns when importing a CSV file?

Explorer

Hello everyone,

I am having a strange problem with importing a csv file. So far all files worked, but from a specific production machine we have the parts from the timestamp are separated by a semicolon (Year;Month;Day;Hour...) from an Excel file like this:

YEAR;MONTH;DAY;HOUR;MINUTE;SECOND;APPTYPE;ERRNO;PARAMETERNO;ACTIVEPUMP;PROGRAMNO;RESULT;EXTRATING;RESBYTE1;FAHRZEUGID1;FAHRZEUGID2;FAHRZEUGID3;FAHRZEUGID4;FAHRZEUGID5;FAHRZEUGID6;FAHRZEUGID7;FAHRZEUGID8;SETAMOUNT_CMM;ACTAMOUNT_CMM;DEVIATION_PERC;APPTIME_MS;MINPRESS_BAR;MAXPRESS_BAR;MEANPRESS_BAR;MEANTORQUE_N;COMPRESSION_CCM;FILLTIME_S;FILLRATE_CCMPS;CALCAMOUNT_CCM
2015;2;11;11;25;51;0;0;0;2;2;2;0;0;0;0;0;0;0;0;0;0;105;109.27826;4.0745325;7.5370002;76.674759;122.46497;99.569862;23656.102;296.64133;6;36.18;112.82711
2015;2;11;11;25;40;0;0;0;2;1;2;0;0;0;0;0;0;0;0;0;0;105;103.19698;-1.7171659;7.9590001;72.3592;129.99324;101.17622;24037.746;397.58435;6;36.18;105.37886

The auto time stamp recognition does not work and assigns the present actual time stamp. Can I tell splunk that in each CSV the first 5 columns (or separated fields) are the timestamp? Btw: When importing the csv Splunk does not display it in the preview in the right order. Could that mean anything?

Thanks again in advance, during this week this community helped me a lot!

Tags (3)
0 Karma

SplunkTrust
SplunkTrust

Your props.conf file should look something like this:

[ csv ]
TIME_FORMAT=%Y %-m %-d %H %M %S
TIMESTAMP_FIELDS=YEAR,MONTH,DAY,HOUR,MINUTE,SECOND
SHOULD_LINEMERGE=false
INDEXED_EXTRACTIONS=csv
FIELD_DELIMITER=;
NO_BINARY_CHECK=true
KV_MODE=none
disabled=false
pulldown_type=true

A little tricky, but it did work for me. It also doesn't make much sense, but sometimes it doesn't have to if it works. The TIME_FORMAT is applied after the TIMESTAMP_FIELDS and FIELD_DELIMITER, so it is confusing.

0 Karma

SplunkTrust
SplunkTrust

Hi ToniSchulz,

take a closer look at the docs here http://docs.splunk.com/Documentation/Splunk/6.2.2/Data/Configuretimestamprecognition
In there you can find examples how this can be done using props.conf and transforms.conf

Hope this helps ...

cheers, MuS

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!