How to change the output in this format?

[m7787580]

Hi Splunker,

Originally I have an output like this as a raw event in Splunk:-

2018-07-17 14:56:08 MIR="TUE, 17-JUL-2018", D_0="-", D_1="2", D_2="4", D_3="-", D_4="-", D_5="-", D_6="2", D_7="-", D_8="-
    ", D_9="2", D_10="-", D_11="-", D_12="-", D_13="-", D_14="-", D_15="-", D_16="-
    ", D_17="-", D_18="-", D_19="-", D_20="-", D_21="-", D_22="-", D_23="-
    ", TOTAL="10"

Where D_0 is 00:00 HR , D_1 is 01:00 HR AM,D_2 is 02:00HR  AM similarly D_23 is 23:00 HR .

I would like to change it to below format:-

TIME                                            VALUE
2018-07-17 00:00                               -
2018-07-17 01:00                               2 
2018-07-17 02:00                               4
2018-07-17 03:00                                -
2018-07-17 04:00                                -
Similarly,  it goes on till 23:00 HR.

Thanks in advance for looking into it

[somesoni2 · Accepted solution]

Give this a try

your current search fetching those raw data fields
| table MIR D_*
| untable MIR Hour VALUE
| eval _time=strptime(MIR,"%a, %d-%b-%Y")+ tonumber(replace(Hour,"D_",""))*3600
| table _time VALUE

[m7787580]

Thanks a lot, It works.