Having a problem with timestamps

[crt89]

Good day Splunkers,

I have a csv file exported from a system that I want to feed to Splunk. The timestamp of these events are in 3 different columns (Date, Hour and Min).

For Date column values I have these examples:

6/1/2014 0:00
6/2/2014 0:00
6/3/2014 0:00


When I'm configuring it to Splunk to set timestamp values it will successfully detect the timestamp:

6/1/14 12:00:00.000 AM.

Now I want to modify the values for hour and minutes which are in the column values (Hour and Min) in my csv.

I was trying to do it with timestamp format:

%Y/%m/%d.*?,%H,%M

but can't make it to go to work. There was also this option that says "Specify all the fields which constitute the timestamp. ex: field1,field2,...,fieldn" but I'm not familiar with it. I think it is new with the latest version. I am currently using v.6.1.1.

Here's a sample from my csv file:

Date	Hour	Min	CONTROLTYPE	ACTIV_CTRL_SES
6/1/2014 0:00	15	45	gx	32
6/1/2014 0:00	8	45	gx	0
6/1/2014 0:00	7	15	gx	0
6/1/2014 0:00	14	45	gx	1
6/1/2014 0:00	4	30	gx	1067

So I'm seeking help from you guys. Thanks !

[lguinn2 · Accepted solution]

TIME_FORMAT must be specified using strptime format (strptime definition)

There is no syntax in strptime for "skip some characters" - you are trying to use a regular expression and that doesn't work.

In 6.1.1, you can do indexed time field extractions for a CSV file and specify the fields that make up the timestamp, as you mentioned. That isn't going to help in this case though.

The problem is, your Date field contains an incorrect specification of the time. That is what you are trying to work around: the fact that your input file is broken.

The only real solution is to fix your Date field. You don't have to make it the right time, just set the output format so it doesn't print the time at all. Then you could use either the TIME_FORMAT specifier in props.conf or indexed field extractions.

[crt89]

Hi @lguinn thanks for your reply. This is actually what I had in mind, the source file was extracted to a separate system I would just ask if the Date field value can be changed removing the time as saving it to csv. We are also trying to make this automated so that the user won't have to manually edit the file. Thanks again

[richgalloway]

The timestamp format you tried is not in the right order. Have you tried '%m/%d/%Y.*?,%H,%H'?