How do I extract this date and time from a string in the format YYYYMMDDHHMMSS, and output it as DD:MM:YYYY HH:MM:SS AM?

[nilotpaldutta]

Hi,

I have a search that gives me the following output:

/u01/splunk/etc/apps/sampleApp/data/order-20151203120002.log

How can I extract the date and time from the above output and show it in a column like:

03:12:2015 12:00:02 AM (or PM)

Looking forward to your help. Thanks in advance.

[ludoz13 · Accepted solution]

Hi,

Have you try this :

...... | rex field=test ".*-(?P\d+)\.log$" | eval date=strptime(date,"%Y%m%d%H%M%S")  | convert timeformat="%d:%m:%Y %H:%M:%S" ctime(date)

In my side, it seems to work

| stats count | eval test="/u01/splunk/etc/apps/sampleApp/data/order-20151203120002.log"| rex field=test ".*-(?P\d+)\.log$" | eval date=strptime(date,"%Y%m%d%H%M%S")  | convert timeformat="%d:%m:%Y %H:%M:%S" ctime(date)

LudoZ

[nilotpaldutta]

Hi lodoz13,
Thanks for the answer.

I am getting this error now -

Error in 'rex' command: Encountered the following error while compiling the regex '.*-(?P\d+)\.log$': Regex: unrecognized character after (?P

This is my search:

index="_index"| dedup source | sort -source | dedup sourcetype | table sourcetype, source | rex field=source ".*-(?P\d+)\.log$" | eval date=strptime(date,"%Y%m%d%H%M%S")  | convert timeformat="%d:%m:%Y %H:%M:%S" ctime(date)

Am I doing something wrong?

TIA

[nilotpaldutta]

It's working now. Just had to add escape character before d+ and add a place holder for the extracted field.

index="_index"| dedup source | sort -source | dedup sourcetype | table sourcetype, source | rex field=source ".*-(?P<date>\d+)\.log$" | eval date=strptime(date,"%Y%m%d%H%M%S")  | convert timeformat="%d:%m:%Y %H:%M:%S" ctime(date)

Thanks for your help. I'm accepting your answer.