Splunk Search

Why is the strptime() eval not returning anything?

loganjwb
Engager

I am using imported CSV data to search throughout Splunk and the CSV file defines the column TIME and only includes the year and month in the format YYYY-MM. I am attempting to convert that field into a UTC UNIX timestamp using the strptime() function but have not had any success. 

This is an image of the extracted fields with a basic search:

Splunk-Fields-Example.png

These were the searches I used when attempting to use the strptime() function. All of the examples did not work.

 

 

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval TIME=strptime(TIME, "%Y-%m")

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval TIME=TIME."-00:00:00:00", TIME=strptime(TIME, "%Y-%m-%d:%H:%M:%S")

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval my_time=strptime('TIME', "%Y-%m")

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval my_time=strptime(YEAR.MONTH, "%Y-%m")

 

 

Additionally, I also tried using the convert command and that didn't work either. Both of the examples below did not work.

 

 

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | convert timeformat="%Y-%m" mktime(TIME) AS NEW_TIME

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval TIME=TIME."-00:00:00:00" | convert timeformat="%Y-%m-%d:%H:%M:%S" mktime(TIME) AS NEW_TIME

 

 

 

Any advice is appreciated, thank you.

Labels (4)
Tags (2)
0 Karma
1 Solution

shivanshu1593
Builder

Hello,

It won't return any results because Splunk won't be able to calculate the epoch time due to the format of the TIME field that you have. Epoch time is the number of seconds elapsed since January 1st, 1970. Since your field TIME does not contain the day of the month, no results would be returned.

The minimum requirement of epoch time conversion is YYYY-MM-DD (which Splunk should handle by adding the first day of the month automatically). If you can add the day in the TIME variable (Ex: 2022=05-12), then you'll be able to perform the operation. Following is a run anywhere example for the same.

 

 

| makeresults
| eval TIME = now()
| eval TIME = strftime(TIME, "%Y-%m-%d")
| fields - _time
| eval TIME = strptime(TIME, "%Y-%m-%d")

 

 

 

Hope this helps. 
###If this helps, kindly consider accepting as an answer/upvote###

Thank you,
Shiv
###If you found the answer helpful, kindly consider upvoting/accepting it as the answer as it helps other Splunkers find the solutions to similar issues###

View solution in original post

0 Karma

shivanshu1593
Builder

Hello,

It won't return any results because Splunk won't be able to calculate the epoch time due to the format of the TIME field that you have. Epoch time is the number of seconds elapsed since January 1st, 1970. Since your field TIME does not contain the day of the month, no results would be returned.

The minimum requirement of epoch time conversion is YYYY-MM-DD (which Splunk should handle by adding the first day of the month automatically). If you can add the day in the TIME variable (Ex: 2022=05-12), then you'll be able to perform the operation. Following is a run anywhere example for the same.

 

 

| makeresults
| eval TIME = now()
| eval TIME = strftime(TIME, "%Y-%m-%d")
| fields - _time
| eval TIME = strptime(TIME, "%Y-%m-%d")

 

 

 

Hope this helps. 
###If this helps, kindly consider accepting as an answer/upvote###

Thank you,
Shiv
###If you found the answer helpful, kindly consider upvoting/accepting it as the answer as it helps other Splunkers find the solutions to similar issues###
0 Karma

PickleRick
Ultra Champion

Sorry, but I beg to differ here as to the cause. Any timestamp format that doesn't include precise information up to the second resolution "doesn't contain absolute time". Yet, for most cases strptime simply assumes the unfilled fields are at their minimum values and gets on with it. So if you don't give hour or minute information, it will happily accept that it's midnight or a full hour. Logically, it should do the same here - assume that as we're not providing a day number, it's the first of the month, the midnight starting the month. But for some reason sometimes splunk doesn't parse such timestamps properly. Maybe it assumes that any non-filled value defaults to 0 and with day number it's kinda ridiculous assumption. But that's just my wild guess.

 

0 Karma

shivanshu1593
Builder

Agreed. The term absolute time shouldn't have been used in the answer. I have edited it. Was thinking in a different direction, typed half of the answer and then the chain of thoughts changed. Since Splunk handles the missing %H:%M:%S gracefully, it should with the date too. 

Thank you,
Shiv
###If you found the answer helpful, kindly consider upvoting/accepting it as the answer as it helps other Splunkers find the solutions to similar issues###
0 Karma

PickleRick
Ultra Champion

Well... strange. Apparently, strptime() doesn't parse the date in some cases. The workaround here would be to "glue" a constant "-01" to the date and strptime with "%Y-%m-%d" but in general, it looks like a bug.

0 Karma

shivanshu1593
Builder

Agreed. Since Date is a mandatory requirement for calculating Epoch time, Splunk might consider adding the first day of the month by itself., instead of throwing blank results.

Thank you,
Shiv
###If you found the answer helpful, kindly consider upvoting/accepting it as the answer as it helps other Splunkers find the solutions to similar issues###
0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...