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.

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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...