Date Range Search on DateTime Field

[matthewcanty]

Hi, I have a field which contains a DateTime. I want to be able to search between a range of Dates on this as opposed to _time.

Example log:
2012-06-25 07:17:19.6676 Action="DALCacheInsert" ... Date="28/06/2012 23:00:00" ...

I want to be able to search Date>"28/06/2012 00:00:00"

[kristian_kolb · Accepted solution]

Hi,

Have you looked at the strptime function for eval? This will let you create a new field in which you convert your Date string to epoch. I don't believe you can perform operations like greater-than or less-than directly on strings like your Date.

... | eval blah = strptime(Date, "%d/%m/%Y %H:%M:%S")

Unfortunately this requires you to specify your time constraints in epoch which is not as easy as one would like. However, you can do the same trick again in the search, so that it reads;

...| eval mylimit=strptime("24 Jun 2012 23:52:55","%d %b %Y %H:%M:%S")
| eval blah = strptime(Date, "%d/%m/%Y %H:%M:%S")
| where blah < mylimit

As you can see there are different date patterns used, and you can use whichever you like AS LONG AS the strptime pattern matches the string. The format of the date string is already set in the event, so the pattern for that is set, but the date string used for mylimit is determined by you, so you can use any format you like, just make sure that the pattern (i.e. "%Y %m %d" etc etc) matches.

If you don't want to complicate things, use the same for both Date and mylimit, and write your mylimit string accordingly.

For more information see;

http://strftime.org/

http://docs.splunk.com/Documentation/Splunk/5.0/SearchReference/CommonEvalFunctions

---

UPDATE:

The string and pattern within the strptime function must match in order for this to make sense:

string           pattern
22Apr2004        %d%b%Y
2001-11-14       %Y-%m-%d
Dec 24, 1988     %b %d, %Y

If you do not specify a time part of the string and pattern, the start of the day will be used (00:00:00), so your search from the 26th to the 30th would not include any events from the 30th.

Also, I'm not sure that you should use earliest and latest, since they are reserved words in splunk, and will act as constraints on _time.

Hope this helps,

/Kristian

[kristian_kolb]

Hmm, I just tested that, and it didn't work for me either, until I changed the last search expression to a where;

| where (d > e) AND (d < l)

Don't know why, I thought they would work the same here.

/k

[matthewcanty]

Still nothing. Search:

host="APP3018-TSDAL"
| eval e=strptime("1/1/2012","%d/%m/%Y")
| eval l=strptime("1/1/2013","%d/%m/%Y")
| eval d=strptime(Date, "%D/%M/%Y %H:%M:%S")
| search d>e AND d<l

Example expected event/result:

2012-06-25 11:29:00.4658 Action="DALCacheUpdate" Id="251234055" Description="Degerfors v Trelleborgs" SportId="Football" CompetitionId="Superettan" Date="25/06/2012 17:59:00" Source="..."

[kristian_kolb]

see update above.
/k

[matthewcanty]

Okay so when I do this:

host="APP3018-TSDAL"
| eval earliest = strptime("26 Jun 2012","%d%b%Y %H:%M:%S")
| eval latest = strptime("30 Jun 2012","%d%b%Y %H:%M:%S")
| eval date = strptime(Date, "%d/%m/%Y %H:%M:%S")
| search date > earliest AND date < latest

I should be getting some results between 26th and 30th June - there are definitely some. However it is returning nothing.