How to write stats to find max value of dynamic host and field

[TheGU]

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.

[somesoni2]

The peak and avg is calculated based on which field?

[TheGU]

port1 port2 port3 field.

[gcusello]

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

[TheGU]

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.