Why are my strftime searches not returning any results?

[paulalbert11]

Simple question: both of these return null. Any idea why?

| eval createDt1 = strftime("2013-03-22 11:22:33","%s")

| eval createDt2 = strftime("20130322112233Z","%s")

[paulalbert11 · Accepted solution]

Thanks so much for the answers; they helped me get close.

I finally figured out the problem. The way we handle LDAP timestamps in our instance is that they are strings, and I had to reconstruct them. We have two timestamps, and even though they appear to have the same syntax in the output view, they have different characters.

As was suggested I used strptime to get epoch time. I also learned that if you want to subtract _time from a variable, you need to assign it to a variable.

| ldapsearch domain=ED search="(&(objectClass=eduPerson)(weillCornellEduCWID=xyz))" attrs="ID,createTimestamp,modifyTimestamp"
| eval z = substr(createTimestamp,1,4) . "-" . substr(createTimestamp,5,2) . "-" . substr(createTimestamp,7,2) . " " . substr(createTimestamp,9,2) . ":" . substr(createTimestamp,11,2) . ":" . substr(createTimestamp,13,2)
| eval q = substr(modifyTimestamp,1,4) . "-" . substr(modifyTimestamp,6,2) . "-" . substr(modifyTimestamp,9,2) . " " . substr(modifyTimestamp,12,2) . ":" . substr(modifyTimestamp,15,2) . ":" . substr(modifyTimestamp,18,2)
| eval createTime=strptime(z,"%Y-%m-%d %H:%M:%S")
| eval modifyTime=strptime(q,"%Y-%m-%d %H:%M:%S")
| eval systemTime=_time
| eval createDiff = systemTime - createTime
| eval modifyDiff = systemTime - modifyTime
| fields - _*
| fields ID, systemTime, createTime, modifyTime, createDiff, modifyDiff, z, q

I hope this is helpful to someone else.

[richgalloway]

Please accept an answer to help future readers.

[aaraneta_splunk]

@paulalbert11 - Did one of the answers below help provide a solution your question? If yes, please click “Accept” below the best answer to resolve this post. If no, please leave a comment with more feedback. Thanks.

[somesoni2]

There are two timeformat conversion functions available with eval (and where) command,
1) strftime - this converts an epoch (number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970) to a human-readable string formatted string. The format of string is defined by the timeformat parameters provided in the command.
Example (run-anywhere search)

| gentimes start=-1 | table starttime | eval HumanReadableStarttime=strftime(starttime,"%m/%d/%Y %H:%M:%S %p")

2) strptime - converts a human readable timestamp format to epoch format. The format of the timestamp string should be provided correctly to convert the string to epoch)
Example (run anywhere sample, different format string requires different timeformat argument to the command)

|eval Date="2013-03-22 11:22:33#20130322112233Z" | table Date | makemv Date delim="#" | mvexpand Date | eval strigToEpochDate1=strptime(Date,"%Y-%m-%d %H:%M:%S") | eval strigToEpochDate2=strptime(Date,"%Y%m%d%H%M%SZ") | eval epochToString=strftime(strigToEpochDate1,"%F %T")

What you've as timestamp in your question is string formatted timestamp, thus you'd need strptime with exact timeformat of your data to convert it to epoch. (see example of strptime above)

[niketn]

As you are trying to get epoch time from string, you need to use strptime instead

| eval createDt1 = strptime("2013-03-22 11:22:33","%Y-%m-%d %H:%M:%S")
| eval createDt2 = strptime("20130322112233Z","%s")

strftime is used to convert epoch time to string time. Hence should not be used here.

[SierraX]

wow... this would be an epoch time on createDt2:

GMT: Fri, 27 Nov 2607 20:08:32.233 GMT 😄

 | eval createDt2 = strptime("20130322112233Z","%Y%m%d%H%M%SZ")

should bring the requested result

[niketn]

As far as you are providing string as quotes it is string not epoc time.
You can always try two consecutive evals to validate whether it is string time (i.e. using strftime) or epoch time (i.e. using strptime)

| eval createDtTest1 = strptime ( YourTimeField, "%Y%m%d%H%M%SZ")

| eval createDtTest2 = strftime ( YourTimeField, "%Y%m%d%H%M%SZ")

| table YourTimeField, createDtTest1, createDtTest2

[SierraX]

You wrote strptime("20130322112233Z","%s") in your answer...

%s is parsing the String as epoch time or am I wrong?

[gokadroid]

Since strftime takes an epoch as first argument and gives back human readable time as per the second argument specified in time format strings. Example eval xx=strftime(1478918100, "%Y-%m-%d %H:%M:%S"), so first argument has to be epoch for it to succeed.

Use accordingly as per directions here in date time functions.
http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/CommonEvalFunctions#Date_and_Time_...