https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287431#M87007 <P>I have b/w data from multiple switch<BR /> <CODE>sourcetype=switch</CODE></P> <PRE><CODE>_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 ... </CODE></PRE> <P>and lookup table with service name information about switch and port it running <BR /> <CODE>[| inputlookup servicename.csv ]</CODE></P> <PRE><CODE>service | cap | switch1 | port1 | switch2 | port2 db1 | 100 | sw1 | 1 | sw2 | 1 db2 | 200 | sw1 | 3 | sw2 | 3 </CODE></PRE> <P>Next I want to run <CODE>stats</CODE> to calculate maximum and average use of each port on each service and expect result to be something like this</P> <PRE><CODE>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% </CODE></PRE> <P>I try to use <CODE>transpose</CODE>, <CODE>subsearch</CODE>, <CODE>join</CODE> or <CODE>append</CODE> with stats to match the data together but not found a work query yet.</P> <P>How to get the value from <CODE>sourcetype=switch</CODE> to run <CODE>max</CODE> and <CODE>avg</CODE> on specific field from table and fill in table service name as show above?</P> <P>edit: actual data contain ~80 switch and 300 services. Each switch have port from 20-80 ports.</P> Fri, 23 Dec 2016 04:26:45 GMT TheGU 2016-12-23T04:26:45Z https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287431#M87007 <P>I have b/w data from multiple switch<BR /> <CODE>sourcetype=switch</CODE></P> <PRE><CODE>_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 ... </CODE></PRE> <P>and lookup table with service name information about switch and port it running <BR /> <CODE>[| inputlookup servicename.csv ]</CODE></P> <PRE><CODE>service | cap | switch1 | port1 | switch2 | port2 db1 | 100 | sw1 | 1 | sw2 | 1 db2 | 200 | sw1 | 3 | sw2 | 3 </CODE></PRE> <P>Next I want to run <CODE>stats</CODE> to calculate maximum and average use of each port on each service and expect result to be something like this</P> <PRE><CODE>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% </CODE></PRE> <P>I try to use <CODE>transpose</CODE>, <CODE>subsearch</CODE>, <CODE>join</CODE> or <CODE>append</CODE> with stats to match the data together but not found a work query yet.</P> <P>How to get the value from <CODE>sourcetype=switch</CODE> to run <CODE>max</CODE> and <CODE>avg</CODE> on specific field from table and fill in table service name as show above?</P> <P>edit: actual data contain ~80 switch and 300 services. Each switch have port from 20-80 ports.</P> Fri, 23 Dec 2016 04:26:45 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287431#M87007 TheGU 2016-12-23T04:26:45Z https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287432#M87008 <P>Hi TheGU,<BR /> try something like this:</P> <PRE><CODE>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 ] </CODE></PRE> <P>Bye.<BR /> Giuseppe</P> Fri, 23 Dec 2016 11:34:03 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287432#M87008 gcusello 2016-12-23T11:34:03Z https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287433#M87009 <P>The peak and avg is calculated based on which field?</P> Fri, 23 Dec 2016 15:31:20 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287433#M87009 somesoni2 2016-12-23T15:31:20Z https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287434#M87010 <P>port1 port2 port3 field.</P> Sun, 25 Dec 2016 14:10:37 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287434#M87010 TheGU 2016-12-25T14:10:37Z https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287435#M87011 <P>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.</P> Sun, 25 Dec 2016 14:18:15 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-stats-to-find-max-value-of-dynamic-host-and-field/m-p/287435#M87011 TheGU 2016-12-25T14:18:15Z