Getting Data In

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

ToniSchulz
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

cpetterborg
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

MuS
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...