I have b/w data from multiple switch
sourcetype=switch
_time | sw | port1 | port2 | port3 | port4
00:01 | sw1 | 10 | 15 | |
00:01 | sw2 | 12 | 17 | 10 | 4
00:11 | sw1 | 7 | 11 | |
00:11 | sw2 | 16 | 30 | 0 | 0
...
and lookup table with service name information about switch and port it running
[| inputlookup servicename.csv ]
service | cap | switch1 | port1 | switch2 | port2
db1 | 100 | sw1 | 1 | sw2 | 1
db2 | 200 | sw1 | 3 | sw2 | 3
Next I want to run stats
to calculate maximum and average use of each port on each service and expect result to be something like this
service | switch1 | port1 | Peak1 | Avg1 | switch2 | port2 | Peak2 | Avg2
db1 | sw1 | 1 | 30% | 7% | sw2 | 1 | 0% | 0%
db2 | sw1 | 3 | 1% | 5% | sw2 | 3 | 49% | 31%
I try to use transpose
, subsearch
, join
or append
with stats to match the data together but not found a work query yet.
How to get the value from sourcetype=switch
to run max
and avg
on specific field from table and fill in table service name as show above?
edit: actual data contain ~80 switch and 300 services. Each switch have port from 20-80 ports.
The peak and avg is calculated based on which field?
port1 port2 port3 field.
Hi TheGU,
try something like this:
your_index sourcetype=switch switch=sw1
| rename sw AS switch
| lookup servicename.csv switch OUTPUT service
| stats values(switch) AS switch1 values(port) AS Port1 max(port) AS Peak1 avg(port) AS Avg1 by service
| rename switch AS switch1
| appendcols [ search
your_index sourcetype=switch switch=sw2
| rename sw AS switch
| lookup servicename.csv switch OUTPUT service
| stats values(switch) AS switch2 values(port) AS Port2 max(port) AS Peak2 avg(port) AS Avg2 by service
| rename switch AS switch2
]
Bye.
Giuseppe
Thanks for your response. However, I edit to add info about actual data. They have a lot of switchs and services so appendcols for each switch might not be a good solution.