Splunk Search

Extracting date from datetime field

rogerbinny
Explorer

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?

Tags (2)
0 Karma
1 Solution

rogerbinny
Explorer

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

View solution in original post

0 Karma

rogerbinny
Explorer

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
Contributor

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

rogerbinny
Explorer

Sorry! Didn't work.. 😞

0 Karma

ramdaspr
Contributor

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

0 Karma

acharlieh
Influencer

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.

0 Karma

rogerbinny
Explorer

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

0 Karma

acharlieh
Influencer

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
Explorer

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

0 Karma

acharlieh
Influencer

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 )

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...