All Apps and Add-ons

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

luislema
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

gokadroid
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

gokadroid
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")

luislema
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.

gokadroid
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

luislema
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.

gokadroid
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

luislema
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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...