topic Re: dd/mm/yyyy is being returned as mm/dd/yyyy in Getting Data In

dd/mm/yyyy is being returned as mm/dd/yyyy

wayneevans — Mon, 12 Dec 2011 16:13:01 GMT

I've been through the forums and this has been asked many times, but I'm not getting anywhere with it. I'm admitting defeat and asking for help

We have a log from a host. The host is in this format:

01/11/11 00:18:05 [ 1] - I) Connected
01/11/11 00:18:05 [ 1] - I) Sending NUA
01/11/11 00:18:05 [ 1] - I) Sarian B: CALL NBMS_HOST01
01/11/11 00:18:06 [ 1] - I) Send data
01/11/11 00:18:06 [ 1] - I) Packet = 1. Dial_count1 = 1

The date above is 1st November, not 11th January. The file is saved at:

c:\trace.log

There is a months worth of data in each file, starting with the 1st of the month. I haven't managed to tell Splunk that the date is in d/m/Y format instead of m/d/Y format. The props.conf file on both the windows and linux boxes contain:

[source::"c:\\trace.log"]
TIME_FORMAT = %d/%m/%Y

[source="c:\trace.log"]
TIME_FORMAT = %d/%m/%Y

[source="c:\\trace.log"]
TIME_FORMAT = %d/%m/%Y

datetime.xml on both the server and windows forwarder has:

<define name="_masheddate3" extract="day, month, year">
    <text><![CDATA[(?:^|source::).*?(?<!\d|\d\.)([012]\d|3[01])(0\d|1[012])(?:20)?([901]\d)(?!\d| {2,})]]></text>
</define>

added and in datePatterns _masheddate3 is added.

(props.conf contains 3 items as I'm trying to get any combination to pickup) I think that is all I need to do, except the results on the web page show:

11/01/2011 00:44:09.000 01/11/11 00:44:09 [ 1] - I) Sarian B: CALL NBMS_HOST01
host=RSCA6976A   Options|  sourcetype=trace-too_small   Options|  source=C:\trace.log   Options

You can see its still picking up US instead of UK date format. First datetime is Splunk Generated - second datetime is data from the log

I'm clearly doing something wrong and would appreciate any ideas as I don't think I'm doing anything too complicated.

Thank you

Re: dd/mm/yyyy is being returned as mm/dd/yyyy

Ayn — Mon, 12 Dec 2011 19:10:41 GMT

Just to check - you are sure that Splunk interprets the date wrong, not just that it's formatting the date wrong when it outputs the events? I.e. these events are really stored in Splunk's index as being from January 11th, not November 1st?

Re: dd/mm/yyyy is being returned as mm/dd/yyyy

wayneevans — Mon, 12 Dec 2011 20:20:49 GMT

It possibly is being displayed wrong and is correct in the db. From what I can see on the screen it my assumption is correct.

Screenshot below:
http://i42.tinypic.com/2zp5jjs.png

I am using en-GB, but I have tried en-US on the URL too. I get the same display.

The data on the screenshot I've manually added too while I'm testing, which is why there is some future dates.

Re: dd/mm/yyyy is being returned as mm/dd/yyyy

kristian_kolb — Mon, 12 Dec 2011 21:07:16 GMT

Well it could be that you state the year portion of the timestamp wrong

TIME_FORMAT = %d/%m/%Y    21/04/2011
TIME_FORMAT = %d/%m/%y    21/04/11

/kristian

Re: dd/mm/yyyy is being returned as mm/dd/yyyy

wayneevans — Tue, 13 Dec 2011 11:53:39 GMT

Genius! Lower-case y

For my benefit when I come here next time forgetting what I've done, on the Server:
/opt/splunk/etc/system/local/props.conf

[source::c:\CCard\trace.log]
TIME_FORMAT = %d/%m/%y %H:%M%:%S

Thank you Kristian

Re: dd/mm/yyyy is being returned as mm/dd/yyyy

kristian_kolb — Tue, 13 Dec 2011 11:58:47 GMT

you're most welcome. /k