topic Re: Splunk seems to be ignoring TIME_FORMAT setting in Getting Data In

Splunk seems to be ignoring TIME_FORMAT setting

dbryan — Wed, 09 Jan 2013 04:25:22 GMT

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?

Re: Splunk seems to be ignoring TIME_FORMAT setting

kristian_kolb — Wed, 09 Jan 2013 09:17:04 GMT

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

Re: Splunk seems to be ignoring TIME_FORMAT setting

dbryan — Wed, 09 Jan 2013 22:42:12 GMT

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.

Re: Splunk seems to be ignoring TIME_FORMAT setting

daniel_splunk — Mon, 28 Sep 2020 13:04:43 GMT

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

Re: Splunk seems to be ignoring TIME_FORMAT setting

gkanapathy — Thu, 17 Jan 2013 02:58:34 GMT

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

Re: Splunk seems to be ignoring TIME_FORMAT setting

gkanapathy — Thu, 17 Jan 2013 03:15:27 GMT

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.