Splunk Search
Highlighted

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
Highlighted

Re: Extracting date from datetime field

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
Highlighted

Re: Extracting date from datetime field

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
Highlighted

Re: Extracting date from datetime field

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

Highlighted

Re: Extracting date from datetime field

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
Highlighted

Re: Extracting date from datetime field

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
Highlighted

Re: Extracting date from datetime field

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
Highlighted

Re: Extracting date from datetime field

Explorer

Sorry! Didn't work.. 😞

0 Karma
Highlighted

Re: Extracting date from datetime field

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
Highlighted

Re: Extracting date from datetime field

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)