Getting Data In

Splunk seems to be ignoring TIME_FORMAT setting

dbryan
Path Finder

I have an event in CSV format like this:

1/11/2012 3:23 PM,0,1,1

These dates are in the date format common to Australia (DD/MM/YYYY). For all days prior to the 13th of each month, some (but not all) events are being parsed as if they were using the date format prevalent in the US (MM/DD/YYYY); there is inconsistency even within a single file.

The correct strptime format for this date string is:

%d/%m/%Y %I:%M %p

As demonstrated in the following program:

#define _XOPEN_SOURCE
#include <time.h>
#include <stdio.h>
int main(void) {
    struct tm thetime;
    char *not_proced = strptime("1/11/2012 3:23 PM", "%d/%m/%Y %I:%M %p", &thetime);
    printf("Month: %d\n", thetime.tm_mon);
    // prints 10, representing November
    printf("Not processed: %s\n", not_proced);
    // prints an empty string
    return 0;
}

So my stanza in profs.conf looks like this:

[mysourcetype]
TRANSFORMS-mysourcetype = remove_headers
CHECK_FOR_HEADER = true
SHOULD_LINEMERGE = false
MAX_TIMESTAMP_LOOKAHEAD=20
TIME_PREFIX=^
TIME_FORMAT="%d/%m/%Y %I:%M %p"

I've tried it with and without each of the three time settings fields, and without the quotes around the format string.

There are no relevant errors in the _internal index about timestamp parsing.

Why is Splunk guessing the time format when I've hard coded it?

Tags (4)
0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee

You should not have quotes in the format string. There is something off in the configuration you have provided. Basically if Splunk is unable to match according to what's in TIME_FORMAT/TIME_PREFIX/etc in props.conf, it will fall through to the internal default rules (which are defined in etc/datetime.xml).

I would not entirely take the opengroup docs as absolutely definitive, as Splunk has a couple of extensions to the format. But do I think that if you data is in fact in that single-line CSV format, and the timestamp is the first entry, you should be able to use:

SHOULD_LINEMERGE = false
TIME_PREFIX = ^
MAX_TIMESTAMP_LOOKAHEAD = 20
TIME_FORMAT = %d/%m/%Y %I:%M %p

Another possibility I suppose is that the sourcetype simply isn't getting applied to your data, and it's still using the defaults.


A quick and dirty hack you can use to try to see what might be the case would be to modify the default fall-through rules in datetime.xml file and switch the order of the "usdate" and "eurodate" formats listed in the "datePatterns" section of the file. You can do this a couple of ways. One way, just copy the file to a new location, make the changes, then add:

DATETIME_CONFIG = /etc/newdatetime.xml

(path is relative to SPLUNK_HOME) to your sourcetype. If that works, then it indicates that the sourcetype is matching, but for some reason the specified TIME_FORMAT isn't being applied. The second test would simply be modify the original datetime.xml. If that has no effect, it would indicate that the sourcetype matching is not right for some reason.

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

You should not have quotes in the format string. There is something off in the configuration you have provided. Basically if Splunk is unable to match according to what's in TIME_FORMAT/TIME_PREFIX/etc in props.conf, it will fall through to the internal default rules (which are defined in etc/datetime.xml).

I would not entirely take the opengroup docs as absolutely definitive, as Splunk has a couple of extensions to the format. But do I think that if you data is in fact in that single-line CSV format, and the timestamp is the first entry, you should be able to use:

SHOULD_LINEMERGE = false
TIME_PREFIX = ^
MAX_TIMESTAMP_LOOKAHEAD = 20
TIME_FORMAT = %d/%m/%Y %I:%M %p

Another possibility I suppose is that the sourcetype simply isn't getting applied to your data, and it's still using the defaults.


A quick and dirty hack you can use to try to see what might be the case would be to modify the default fall-through rules in datetime.xml file and switch the order of the "usdate" and "eurodate" formats listed in the "datePatterns" section of the file. You can do this a couple of ways. One way, just copy the file to a new location, make the changes, then add:

DATETIME_CONFIG = /etc/newdatetime.xml

(path is relative to SPLUNK_HOME) to your sourcetype. If that works, then it indicates that the sourcetype is matching, but for some reason the specified TIME_FORMAT isn't being applied. The second test would simply be modify the original datetime.xml. If that has no effect, it would indicate that the sourcetype matching is not right for some reason.

gkanapathy
Splunk Employee
Splunk Employee

definitely you should not have quotes are around the format string, or else it will require them in the timestamp.

daniel_splunk
Splunk Employee
Splunk Employee

What is the data looks like when you have problem.

This props.conf works with this data.

1/11/2012 3:23 PM,0,1,1 linea

2/11/2012 3:23 PM,0,1,1 lineb

14/11/2012 3:23 PM,0,1,1 linec

5/12/2012 3:23 PM,0,1,1 lined

props.conf

NO_BINARY_CHECK=1
SHOULD_LINEMERGE=false
TIME_FORMAT=%d/%m/%Y %I:%M %p

0 Karma

kristian_kolb
Ultra Champion

I believe that %d may not be the correct choice, since it implies that there should be leading zeroes.
Try with %e instead (no leading zeroes)

As for the hour, you could try substituting %I with %l (no leading zeroes)

See www.strftime.net

Hope this helps,

Kristian

dbryan
Path Finder

According to http://pubs.opengroup.org/onlinepubs/009695399/functions/strptime.html, which is what the Splunk documentation suggests Splunk uses, "leading zeros are permitted but not required" for %d and also %e by implication. I tried it with %e and there was no change.

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 ...