All Apps and Add-ons

What SPL syntax do I use to represent "Beginning and end of current date" to filter my search results?

Path Finder

I have a field called Date where I store the event dates, and I need to filter on that field to display records only for the current date. This is my search:

source=revenue Date >= "BeginningOfDay" Date <= "EndOfDay". 

What expressions do I use for "BeginningOfDay" and "EndOfDay"?

My Date is formatted like this: "2016-12-10 17:00:38.967"

1 Solution

Motivator

If you are trying in the search and events are indexed when they occur then you can use following assuming an event which hasn't occurred might not be required to be filtered as now() represents right now when you are running the search.

index=yourIndex sourcetype=yourSourcetype earliest=@d latest=now()
| complete your search

Updating as per comments

your query to return events
| eval dateInField=strptime(Date, "%Y-%m-%d %H:%M:%S.%3N")
| table the fields of interest, dateInField
| where dateInField >= relative_time(now(), "@d") AND dateInField < relative_time(now(), "@d+24h")

View solution in original post

Motivator

If you are trying in the search and events are indexed when they occur then you can use following assuming an event which hasn't occurred might not be required to be filtered as now() represents right now when you are running the search.

index=yourIndex sourcetype=yourSourcetype earliest=@d latest=now()
| complete your search

Updating as per comments

your query to return events
| eval dateInField=strptime(Date, "%Y-%m-%d %H:%M:%S.%3N")
| table the fields of interest, dateInField
| where dateInField >= relative_time(now(), "@d") AND dateInField < relative_time(now(), "@d+24h")

View solution in original post

Path Finder

Unfortunately the events are not indexed at the time they occur, sometimes there's a delay of up to 5 minutes. That's why I need to rely on my date instead of the the index date.

Motivator

can you please post the format in which the field Date is storing the value so that only then can it be decided how to do a comparison (whether epoch or string comparison etc)

0 Karma

Path Finder

I tried but I received this message: "You are only allowed to submit 2 posts per day until you reach 40 points of reputation level." I will do it tomorrow.

Motivator

Ok, so lets say your field Date has date as String %m-%d-%Y (which you can replace it with the actual format) then first try to convert it to an epoch time like this:

| eval dateInField=strptime(Date,"%m-%d-%Y")

Now you can compare on this with today's date as
where dateInField >= relative_time(now(), "@d")

or if you want to be more strict then
where dateInField >= relative_time(now(), "@d") AND dateInField < relative_time(now(), "@d+24h")

So your final query becomes as

your query to return events
| eval dateInField=strptime(Date,"%m-%d-%Y")
| table the fields of interest, dateInField
| where dateInField >= relative_time(now(), "@d")

or just change the last line with
| where dateInField >= relative_time(now(), "@d") AND dateInField < relative_time(now(), "@d+24h")

Updating the answer as well.

0 Karma

Path Finder

My "Date" field is formatted like this "2016-12-10 02:10:39.973". I tried this:
eval dateInField=strptime(Date,"%m-%d-%Y") and it didn't create another field "dateInField". I've tried this before, but it's not evaluating my Date. I think it's because of the date format.

0 Karma