Splunk Search

How to convert time from an upload csv to Splunk readable format

phamxuantung
Communicator

I have a csv file that I upload through Lookup Editor which have a Time column in this format

15/06/2021 14:35:00

I want to convert it to Splunk readable time or an Unix time format so I can filter out the row between two certain date (between 14/06/2021 and 7/7/2021).

I have try

|inputlookup sample.csv
|eval time = strptime(Time,"%m/%d/%Y %I:%M:%S %p")
|table time 

But it return "No result found".

How do I go about this? Or my strptime have any errors in formatting?

Labels (2)
0 Karma

mayurr98
Super Champion

Hi its because you are using wrong time format:

 

use this:

 

| eval time = strptime(Time,"%d/%m/%Y %H:%M:%S")

Accept/upvote if this helps!

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Apparently either your lookup doesn't have field called Time or you gave wrong time format to strptime (which you did - there is no " %p" part in there for sure).

0 Karma

phamxuantung
Communicator

My csv file do have a 'Time' field, and even when I change my eval to

eval time = strptime(Time, "%m/%d/%Y %H:%M:%S")

it still return nothing as show below.

Capture.PNG

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Based on the sample data and screenshot, I am convinced that the first number is %d and the second is %m.

| eval time = strptime(Time, "%d/%m/%Y %H:%M:%S")
0 Karma
Get Updates on the Splunk Community!

The All New Performance Insights for Splunk

Splunk gives you amazing tools to analyze system data and make business-critical decisions, react to issues, ...

Good Sourcetype Naming

When it comes to getting data in, one of the earliest decisions made is what to use as a sourcetype. Often, ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...