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
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
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
Thanks a lot @woodcock, It work !
I'm looking att your "should-be-accepted...........answer" too !
Thanks
If any answer over there helps, be sure to up-vote.
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