How to edit my search to transpose data field (in column) to header field?

[rflouquet]

Hello,

I'm trying to get this table :

Device ----- Interface ----- March ----- April ----- .....

device1 ----- interface1 ----- LoaddurationM1 ----- LoaddurationA1 ----- .....

device2 ----- interface2 ----- LoaddurationM2 ----- LoaddurationA2 ----- .....

But after running my search I'm getting the table :

Month ----- Device----- Interface----- Load Duration
March----- device1 ----- interface1----- LoaddurationM1
April----- device1 ----- interface1----- LoaddurationA1
April----- device2 ----- interface2----- LoaddurationA2
March----- device2 ----- interface2 ----- LoaddurationM2

I tried the "transpose" command but nothing works.

My search is the next one :

sourcetype=csv Device=*
| eval LD=if(Bits_out_sec>Bandwidth*0.8, Duration, 0)
|stats sum(Duration) AS TotalDuration sum(LD) AS TotalLD BY date_month, Device, Interface
|eval "Load Duration"=round(TotalLD/TotalDuration*100,2)
|table date_month, Device, Interface, "Load Duration"

If you have any idea it would be great.

Thanks a lot,

Romain

[woodcock · Accepted solution]

Try this:

sourcetype=csv Device=*
| eval LD=if(Bits_out_sec>Bandwidth*0.8, Duration, 0)
| stats sum(Duration) AS TotalDuration sum(LD) AS TotalLD BY date_month, Device, Interface
| eval "Load Duration"=round(TotalLD/TotalDuration*100,2)
| eval Device_Interface = Device . ":::" . Interface
| fields - Device Interface
| xyseries Device_Interface date_month "Load Duration"
| rex field=Device_Interface "^(?<Device>.*?):::(?<Interface>.*)$"
| fields - Device_Interface
| table Device Interface *

You will now probably care about the ordering of the months along the top, for that, see my (should-be-accepted-but-unfortunately-hasn't-been) answer on this Q&A:

https://answers.splunk.com/answers/522959/cannot-sort-dynamic-column-in-date-format.html

[rflouquet]

Thanks a lot @woodcock, It work !

I'm looking att your "should-be-accepted...........answer" too !

Thanks

[woodcock]

If any answer over there helps, be sure to up-vote.

[cmerriman]

try this:

sourcetype=csv Device=*
 | eval LD=if(Bits_out_sec>Bandwidth*0.8, Duration, 0)
 |stats sum(Duration) AS TotalDuration sum(LD) AS TotalLD BY date_month, Device, Interface
|eval deviceInterface=Device+" - "+Interface
|eval "LoadDuration"=round(TotalLD/TotalDuration*100,2)
|chart values(LoadDuration) as LoadDuration by deviceInterface
|makemv deviceInterface delim=" - "
|eval Interface=mvindex(deviceInterface ,1)
|eval Device=mvindex(deviceInterface ,0)
|fields - deviceInterface