Splunk Search

strftime and strptime functions are not working in search queries

ygkr
New Member

I have multiple time fields in my db like Reported Date, Last Modified Date, Responded Date.. If I apply strftime/strptime functions on that it is not working someone plz tell me how to do that.

Tags (1)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

I'm guessing your fields are literally called Reported Date, and you tried strptime(Reported Date, "%...")?

If so you will need to enclose the field names in single quotes: strptime('Reported Date', "%...")
Whenever possible you should use field names with alphanumeric and underscore characters to ease eval use. For a worse example, Report+Date would be a valid field name... but eval would interpret that as "Report field plus Date field" without the single quotes.

View solution in original post

0 Karma

DMohn
Motivator

Taking the information from your last comment (Last_Modified_Date being SQL DateTime format) you will have to convert this date into a Unix Timestamp by using strptime before being able to use strftime again.

If your Last_Modified_Date looks like 2016-09-01 10:00:00 (YYYY-MM-DD HH:MM:SS) you may use the following conversion to only have the year (I assume thats what you want):

 your_base_search | eval year=strftime(strptime(Last_Modified_Date,"%Y-%m-%d %H:%M:%S"),"%Y")

You may refer to https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables for more information on the date and time variables of strptimeand strftime

martin_mueller
SplunkTrust
SplunkTrust

I'm guessing your fields are literally called Reported Date, and you tried strptime(Reported Date, "%...")?

If so you will need to enclose the field names in single quotes: strptime('Reported Date', "%...")
Whenever possible you should use field names with alphanumeric and underscore characters to ease eval use. For a worse example, Report+Date would be a valid field name... but eval would interpret that as "Report field plus Date field" without the single quotes.

View solution in original post

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Well, I could keep guessing different things that you may or may not be doing wrong... or you could supply more information. What do those fields look like? What searches have you tried that failed? What does a failed result look like?

0 Karma

ygkr
New Member

source="TDA_APA_Excel Data.csv" host="CDC1-D-6QKN7BS" index="test" sourcetype="test"
| eval time = strftime(_time, "%Y")
| dedup time
| table time

O/P
time↕
2016

2015

2014

2013

2012

2011

This is wt I tried to get the same output as I got for "_time" field as shown above.

source="TDA_APA_Excel Data.csv" host="CDC1-D-6QKN7BS" index="test" sourcetype="test"
| eval time = strftime('Last_Modified_Date', "%Y")
| dedup time
| table time

Obtained O/P: No results found.

0 Karma

DMohn
Motivator

What are the original values of your field Last_Modified_Date? Is it a Unix timestamp or some other date format?

strftime requests the time field to be a Unix timestamp, otherwise it does not know how to transform the date.

ygkr
New Member

Hi DMohn thanks for the response,...
The dataformat for the field Last_Modified_Date is DateTime.
It is not unix timestamp... plz let me knw hw to convert that field to unix timestamp dataformat????

0 Karma

ygkr
New Member

Thanq DMohn.... Its working 🙂

0 Karma

ygkr
New Member

Thanks for the response martin... 🙂
I tried with single quotes even though my datetime fields are not working with strftime and strptime functions as I expected...!!
It is working with only _time field but my requirement is to work with all the datetime fields in the given data like('Reported Date', 'Last_Modified_Date' and 'Last_Resolved_Date'

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.