topic Re: Extracting date from datetime field in Splunk Search

Extracting date from datetime field

rogerbinny — Sun, 08 Feb 2015 11:46:31 GMT

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?

Re: Extracting date from datetime field

acharlieh — Sun, 08 Feb 2015 16:27:29 GMT

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.

Re: Extracting date from datetime field

rogerbinny — Mon, 28 Sep 2020 18:52:37 GMT

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

Re: Extracting date from datetime field

acharlieh — Sun, 08 Feb 2015 20:30:04 GMT

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

Re: Extracting date from datetime field

ramdaspr — Sun, 08 Feb 2015 23:26:42 GMT

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

Re: Extracting date from datetime field

rogerbinny — Mon, 28 Sep 2020 18:55:21 GMT

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

Re: Extracting date from datetime field

rogerbinny — Mon, 09 Feb 2015 19:10:29 GMT

Sorry! Didn't work.. 😞

Re: Extracting date from datetime field

ramdaspr — Mon, 09 Feb 2015 23:59:36 GMT

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

Re: Extracting date from datetime field

somesoni2 — Mon, 28 Sep 2020 18:55:36 GMT

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)

Re: Extracting date from datetime field

acharlieh — Tue, 10 Feb 2015 00:34:49 GMT

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 )

Re: Extracting date from datetime field

rogerbinny — Mon, 28 Sep 2020 18:53:05 GMT

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