help with table column transformation needed

[damucka]

Hello,

I need to transform the table I have from:

_time  avg1  avg2  avg3
t1      v11   v21   v31 
t2      v12   v22   v32 
t3      v13   v23   v33

into

_time   KPI   VALUE 
t1      avg1    v11    
t2      avg1    v12    
t3      avg1    v13
t1      avg2    v21    
t2      avg2    v22    
t3      avg2    v23   
t1      avg3    v31    
t2      avg3    v32    
t3      avg3    v33 

I need this format to create a punchcard visualization out of it later.

How would I achieve this?

Kind regards,
Kamil

[kamlesh_vaghela · Accepted solution]

@damucka

Can you please try this?

YOUR_SEARCH |eval KPI="" | foreach avg* [eval KPI=KPI.","."<<FIELD>>"] | eval KPI=split(KPI,",") | mvexpand KPI | where KPI!="" | eval VALUE=case(KPI="avg1",avg1,KPI="avg2",avg2,KPI="avg3",avg3) | table time KPI VALUE

Sample Search:

| makeresults 
| eval _raw=" _time  avg1  avg2  avg3
 t1      v11   v21   v31 
 t2      v12   v22   v32 
 t3      v13   v23   v33" | multikv forceheader=1 | table time avg1 avg2 avg3 |eval KPI="" | foreach avg* [eval KPI=KPI.","."<<FIELD>>"] | eval KPI=split(KPI,",") | mvexpand KPI | where KPI!="" | eval VALUE=case(KPI="avg1",avg1,KPI="avg2",avg2,KPI="avg3",avg3) | table time KPI VALUE

Thanks