Getting Data In

How do I change the date format from MMDDYYYY to DDMMYYYY to get my expected search results?

pavanae
Builder

The following is my search and its result:

Search 1:

earliest="01/08/2016:00:00:01" latest="01/08/2016:23:59:59" `getABCsWin("XYZ","abc12345678")`

Result 1:
No results found.

Time format used in earliest and latest: DDMMYYYY

Search 2:

earliest="08/01/2016:00:00:01" latest="08/01/2016:23:59:59" `getABCsWin("XYZ","abc12345678")`

Result 2: Expected result acquired.

Time format used in earliest and latest: MMDDYYYY

Now we have been using search 1 from long time to get the details and recently search 1 wasn't displaying any results, so we observed some deviation on Splunk search i.e; instead of our default format which was DDMMYYYY events were indexing with the wrong format i.e; MMDDYYYY. So how can we get the result 2 for the search 1 like before?

Events were as follows:

8/1/16 
4:08:08.000 PM   
 Valid 
08/01/2016 04:08:08 PM
... 12 lines omitted ...

Expected changes for the above events to get it resolved:

1/8/16 
4:08:08.000 PM   
 Valid 
01/08/2016 04:08:08 PM
... 12 lines omitted ...

Here am just looking to change the Date format from MMDDYYYY to DDMMYYYY to get the expected result.

Is it something to be done on props.conf in Splunk or in the default log format?

Sorry if I made you confused with my language.

0 Karma
1 Solution

sideview
SplunkTrust
SplunkTrust

You are supposed to be able to use the timeformat="%d/%m/%Y:%H:%M:%S" argument in your search string to get your desired behavior. It certainly used to work, however it doesn't work anymore and it seems to just be a bug.

As it says in these docs:

(see the listing for "timeformat" https://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/SearchTimeModifiers )

you're supposed to be able to use the timeformat argument in your search strings, to specify exactly what timeformat your string-formatted absolute starttime and endtime terms are using.

For example:
index=_* starttime="06/09/2016:00:00:00" endtime="07/09/2016:00:00:00" timeformat="%d/%m/%Y:%H:%M:%S"

That search should search midnight september 6th to midnight on September 7th. But it doesn't. It searches from June 9th to July 9th. It ignores the timeformat arg completely.

I thought maybe it deferred to locale (ie en-US vs en-GB vs fr-FR in your URL and elsewhere), but it seems to ignore that too. In all cases that I've found it interprets string-formatted absolute starttime/endtime args as US-dates, ie as %m/%d/%Y:%H:%M:%S

Sorry I can't be of more help. I would open a case with Splunk Support to have them investigate. Quite possibly it's a recent regression and you might be able to downgrade as a last resort.

View solution in original post

sideview
SplunkTrust
SplunkTrust

You are supposed to be able to use the timeformat="%d/%m/%Y:%H:%M:%S" argument in your search string to get your desired behavior. It certainly used to work, however it doesn't work anymore and it seems to just be a bug.

As it says in these docs:

(see the listing for "timeformat" https://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/SearchTimeModifiers )

you're supposed to be able to use the timeformat argument in your search strings, to specify exactly what timeformat your string-formatted absolute starttime and endtime terms are using.

For example:
index=_* starttime="06/09/2016:00:00:00" endtime="07/09/2016:00:00:00" timeformat="%d/%m/%Y:%H:%M:%S"

That search should search midnight september 6th to midnight on September 7th. But it doesn't. It searches from June 9th to July 9th. It ignores the timeformat arg completely.

I thought maybe it deferred to locale (ie en-US vs en-GB vs fr-FR in your URL and elsewhere), but it seems to ignore that too. In all cases that I've found it interprets string-formatted absolute starttime/endtime args as US-dates, ie as %m/%d/%Y:%H:%M:%S

Sorry I can't be of more help. I would open a case with Splunk Support to have them investigate. Quite possibly it's a recent regression and you might be able to downgrade as a last resort.

pavanae
Builder

Is there any way to resolve this by using macros.conf

0 Karma

sideview
SplunkTrust
SplunkTrust

Well, it would be nice, but macros.conf ultimately can't do anything for you beyond what you can do yourself in the Search Language. =/ In this case it's the actual searchterm itself that seems to not work.

sideview
SplunkTrust
SplunkTrust

1) Did you by any chance recently upgrade from a much older version of Splunk?

the earliest="01/08/2016:00:00:01" latest="01/08/2016:23:59:59" has really disappeared from usage in recent years. A long time ago I know you used to be able to submit timeformat="%d/%m/%Y %H:%M:%S" but this argument seems to be no longer supported. At least, I can't get it to do anything. Interestingly, Splunk doesn't give any errors about it's presence in my searches.

2) Also, I think that in at least some of those older versions, this behavior was locale-sensitive. ie if you had /en-US/ in your location bar you'd get US English and "%m/%d/%Y %H:%M:%S" behavior, but if you had /en-GB/ you'd get "%d/%m/%Y %H:%M:%S" behavior.

Here's a lot more information about locale, how Splunk knows which to use, and what it does with the info. https://docs.splunk.com/Documentation/Splunk/6.4.3/Admin/Userlanguageandlocale

3) NOTE: although timestamp display is very much locale-sensitive across the Splunk UI, when it comes to absolute times specified as mm/dd/yyyy or dd/mm/yyyy, earliest/latest appears to have been hardcoded to mm/dd/yy for a while now.

sanity checking - is it possible that those searches really weren't returning the events you thought they were? The timeline is often a good clue because it will show neighboring days and therefore you can see which digit is actually incrementing!

0 Karma

pavanae
Builder

So is there any way to change those time_format ?

0 Karma

somesoni2
Revered Legend

Based on the example 3 here, mmddyyyy type of format does work (or should've worked in the past as well. Can you try to run search 2 for historical period?

https://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/SearchTimeModifiers#How_to_specif...

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...