Getting Data In

Having a problem with timestamps

crt89
Communicator

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 !

Tags (3)
0 Karma
1 Solution

lguinn2
Legend

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.

View solution in original post

0 Karma

lguinn2
Legend

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.

0 Karma

crt89
Communicator

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...