Format different dates in Splunk 7.1.1

[snigdhasaxena]

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.

[halisc]

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.

[snigdhasaxena]

Thanks for the response.

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

[snigdhasaxena]

epochtime values are not getting generated

[halisc]

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.

[halisc]

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

[snigdhasaxena]

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

[snigdhasaxena]

epochtime values are not getting generated

[kamlesh_vaghela]

@snigdhasaxena

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

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

[snigdhasaxena]

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