Extracting date from datetime field

[rogerbinny]

Hi

I have field named as "extract_datetime" and it has the following values;
2015-02-08 02:15:24
2015-02-08 02:18:39
2015-02-07 01:38:11
2015-01-28 11:01:00
I want to extract the events which has current date. Lets say today is 8th Feb, i need the first 2 events only. Also there are few values where it has no values (blank). How can i avoid them as well.

I tried using now() and strftime () but no avail. Any pointer in this case?

[rogerbinny · Accepted solution]

Thank you guys for your help! Though my extract_datetime field has %Y-%m-%d %H:%M:%S but when i executed the below search, came to know it is only extracting %Y-%m-%d .

sourcetype="something" extract_datetime= * | table extract_datetime

Hence updated my search string as below and it works perfectly. Thanks again 🙂

sourcetype="something" extract_datetime= *
| WHERE strptime(extract_datetime,"%Y-%m-%d") >= relative_time(now(),"@d")

[somesoni2]

What is the format of the field? Is it a string or Date time value(epoch)? (run this and tell the output formatsourcetype="something" extract_datetime= * | table extract_datetime)

[ramdaspr]

how about converting into a timestamp and then to a date format to compare against now().

.. | where isnotnull(extract_datetime)| eval k=strptime(extract_datetime,"%Y-%m-%d %H:%M:%S") | eval m=strftime(now(),"%d-%m-%Y") | eval o=strftime(k,"%d-%m-%Y") | where o==m | ...

[rogerbinny]

Sorry! Didn't work.. 😞

[ramdaspr]

You can paste out the exact query you are trying?
I tried the one above with my IIS logs to filter out specific days and it seems to work fine..

[acharlieh]

Assuming your field is being extracted as a string I would use something along the lines of |where isnotnull(extract_datetime) and len(extract_datetime) > 0 and strptime(extract_datetime,"format string here, I'm on a cell phone so looking it up would be difficult") >= relative_time(now(),"@d")

Basically we keep those results where the field is a value, and we parse the field to a timestamp (strptime), and keep those only after midnight today (now() taken back to @d). Depending on the behavior of the strptime function, the first two clauses may be unnecessary, but I'd need to try things out on my Splunk instance to be sure.

[rogerbinny]

Thanks for your reply. But unfortunately it didn't work..
I tried with the following option - no result(s) returned.
sourcetype="something" extract_datetime= *
| WHERE strptime(extract_datetime,"%m/%d/%Y") >= relative_time(now(),"@d") -- Replaced -1 & -2 with @d

[acharlieh]

If your field is like "2015-02-08 02:15:24" there's no way that the format "%m/%d/%Y" could match that. For one thing, there are no slashes in your sample data, secondly I think the date fields are in the wrong order, thirdly, you will likely want to include the correct format string for parsing the time portion as well

[rogerbinny]

Sorry, for the format.. Tried the following; but no avail.
sourcetype="something" extract_datetime= *
| WHERE strptime(extract_datetime,"%Y-%m-%d %H:%M:%S") >= relative_time(now(),"@d")

[acharlieh]

What does the job inspector say? Does sourcetype="something" extract_datetime=* return results? (Also I'm not sure if it matters or not but usually I've always written Splunk commands in lower case (e.g. | where instead of | WHERE )