Archive

Format different dates in Splunk 7.1.1

Communicator

We have a Field, say, XYZ with date-time values but format for all values is not same. For some values format is "MM/DD/YYYY HH:MM:SS AM/PM" or "YYYY/MM/DD HH:MM:SS" and so on.

We have to put all the date time values in same format and then calculate the no. of days from each date till today.

Tags (1)
0 Karma

New Member

Hi snigdhasaxena,,

You can start with fixing their formats into something you set.Then you should convert the date into epoch time to make calculations.After setting your format and convert it into epoch time you can substract these dates from today's date and finish the calculation.Lastly you should convert your result into day and finish the query.

1)First set the date format and convert into epoch time.

|eval dateformat=strftime(XYZ,"%Y-%m-%d %H:%M:%S") //Turns you date into 2018-08-22 17:37:15
|eval epochtime=strptime(dateformat, "%Y-%m-%d %H:%M:%S") //Convert to Epoch time.

2)Calculate the duration from your date to today.

| eval dayduration=round((now()-epochtime)/86400) // How many seconds passed till today and convert into the day equivalent.

1 Day= 60*60*24 seconds(86400 seconds).Round for getting rid of decimal.

So dayduration represent you how many days has passed till now.

0 Karma

Communicator

Thanks for the response.

Although the query runs successfully without any error, it is generating field called, dateformat but not epochtime and dayduration.

0 Karma

Communicator

epochtime values are not getting generated

0 Karma

New Member

Can you show some outputs and inputs from your query and data? Problem might be due to your Date Format, you should arrange your query according to your date format.

0 Karma

New Member

kamlesh is right i made a mistake. While converting from String Date format to Epoch time "strptime" function must be used. I edited my answer again

0 Karma

Communicator

Hi,
Thanks for your response but still epochtime nd dayduration fields are not getting generated

0 Karma

Communicator

epochtime values are not getting generated

0 Karma

SplunkTrust
SplunkTrust

@snigdhasaxena

Can you please replace strftime to strptime for epochtime eval and try again?

|eval epochtime=strptime(dateformat, "%Y-%m-%d %H:%M:%S") 
0 Karma

Communicator

Hi,
Thanks for your response but still epochtime nd dayduration fields are not getting generated

0 Karma