Splunk Search

Extracting date from datetime field

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

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" extractdatetime= * | table extractdatetime

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

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

View solution in original post

0 Karma

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" extractdatetime= * | table extractdatetime

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

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

View solution in original post

0 Karma

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" extractdatetime= * | table extractdatetime)

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

Explorer

Sorry! Didn't work.. 😞

0 Karma

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

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

Explorer

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

0 Karma

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

Explorer

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

0 Karma

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