topic Re: Convert Timestamp from one format to UNIX style format in Splunk Search

Convert Timestamp from one format to UNIX style format

dowdag — Mon, 10 Jun 2019 22:20:06 GMT

I have a log file that has the timestamp for each line as:

Jun 10, 11:07:59.305475

Note that the year is missing - it is inferred from file name... or something...
I am good with deriving year from now()

I would like to convert it to:

2019-6-10 11:07:59.305475

Might there be a way to accomplish this when creating a field extraction?

I have had no luck with startime

Thanks for any clues!

Re: Convert Timestamp from one format to UNIX style format

alonsocaio — Tue, 11 Jun 2019 01:56:34 GMT

You can try using this command to format _time:

| eval time_field=strftime(_time,"%Y-%m-%d %H:%M:%S.%6N")

Re: Convert Timestamp from one format to UNIX style format

gcusello — Tue, 11 Jun 2019 06:40:22 GMT

Hi dowdag,
You have to convert two times your timestamp, try something like this:

| eval time_field=strftime(strptime(_time,"%B %d, %H:%M:%S.%6N"),"%Y-%m-%d %H:%M:%S.%6N")

Bye.
Giuseppe

Re: Convert Timestamp from one format to UNIX style format

harshpatel — Tue, 11 Jun 2019 12:11:31 GMT

Hi @dowdag, Are you trying to achieve this using props.conf or you want to do this using a Splunk search?
What I can tell is you are already extracting timestamp using props.conf and you want to add a year to it?

Re: Convert Timestamp from one format to UNIX style format

dowdag — Tue, 11 Jun 2019 18:56:36 GMT

Extracted "date time string" data from log: Jun 06, 11:10:04.307625

I added a lookup table

MonthAbrv, MonthNumber
Jan,01 
Feb,02
Mar,03
etc....

| rex field=TimeStamp "(?<Month>\w+)"
| lookup MonthStrToNum MonthAbrv as Month OUTPUT MonthNumber
| rex field=TimeStamp "\w+\s(?<day>\d+)"
| eval year=strftime(now(), "%Y") 
| rex field=TimeStamp "^.+,\s(?<Time>[\d:.]+)"
| eval DateTimeStr= (year . "-". MonthNumber . "-" . day ." " . Time)

DateTimeStr: 2019-06-06 11:10:04.307625

| eval uxTimeStamp=strftime(strptime(DateTimeStr, "%Y-%m-%d %H:%M:%S:%3N"), "%Y-%m-%d %H:%M:%S:%3N")

However uxTimeStamp is NULL -- what might I have missed?

Thanks for any help

Re: Convert Timestamp from one format to UNIX style format

harshpatel — Wed, 12 Jun 2019 04:58:21 GMT

Hi @dowdag,

You are defining wrong format for DateTimeStr when converting it into epoch time. Please try this:

| eval uxTimeStamp=strftime(strptime(DateTimeStr, "%Y-%m-%d %H:%M:%S.%6N"), "%Y-%m-%d %H:%M:%S:%3N")

See how your DateTimeStr value is 2019-06-06 11:10:04.307625 and as per your format in strptime i.e. %Y-%m-%d %H:%M:%S:%3N means you are expecting DateTimeStr to be 2019-06-06 11:10:04:307 which will result in uxTimeStamp being NULL value.

Cheers,
Harsh

Re: Convert Timestamp from one format to UNIX style format

harshpatel — Wed, 12 Jun 2019 04:59:22 GMT

@dowdag
Please try this:
https://answers.splunk.com/answers/751096/convert-timestamp-from-one-format-to-unix-style-fo.html?childToView=751844#answer-751844