How to use rex and sed to insert '-' and ':' in the result?

[nilotpaldutta]

Hi, I'm new to Splunk. I have a query that extracts the date and time from the name of a log file. Logfile names are like e.g. XXXXXXXX_20150615133030.log. My query successfully returns the desired output which is 20150615133030. This is as per my requirement.

Now, i would like to edit the number to show like this -- "2015-06-15 13:30:30".
I tried the following command in bash prompt and it works -- sed 's/^\(.\{4\}\)\(.\{2\}\)\(.\{2\}\)\(.\{2\}\)\(.\{2\}\)/\1-\2-\3 \4:\5:/g' numbers.txt and it works fine. But this is not working when i use it in my Splunk query.

Please answer if anyone knows. Thanks in advance.

[yannK · Accepted solution]

in the search query, the sed string is between double quotes. Therefore you have to escape or double escape some symbols.

PS: in the props.conf you do not need the extra escape.

[stephanefotso]

Also, why don't you edit your props.conf for it? I think it will be easy!

[nilotpaldutta]

Thanks for your response. Can you please post an example?
I'm not looking to standardize my output. Just need it once for the above query.
I might be wrong but isn't editing any config file going to always return results of other queries also in one particular format?

[richgalloway]

What is the Splunk query that is failing?

[nilotpaldutta]

index=myindex | dedup source | sort -source | dedup sourcetype | rex field=source mode=sed "s/[^0-9]*//g" | rename source as date | rex field=date mode=sed "s/(\d{4}-){1}/2015-/g" | table sourcetype, date

source and sourcetype are two fields i'm retrieving.

[richgalloway]

Your search is failing because the date field does not have a hyphen in it. This should work (your original sed string has far too many escapes).

rex field=date mode=sed "s/(.{4})(.{2})(.{2})(.{2})(.{2})/\1-\2-\3 \4:\5:/"

[nilotpaldutta]

Thank you. This works for me. 🙂

[richgalloway]

Please accept the answer.