https://community.splunk.com/t5/Splunk-Search/How-to-get-full-row-based-on-max/m-p/333488#M99184 <P>Great, it works!</P> <P>The second requirement was related to the need to avoid multivalue fields in the following case</P> <P>CI ReqWeekAva Cluster_Ava_Code<BR /> App1 10 CAC1<BR /> App1 40 CAC2<BR /> App1 30 CAC3<BR /> App1 40 CAC2</P> <P>I mean avoid output like</P> <P>App1 40 CAC1<BR /> 40 CAC1</P> <P>But your solution it's fine, so no issue <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Tue, 29 Sep 2020 17:50:06 GMT CarmineCalo 2020-09-29T17:50:06Z https://community.splunk.com/t5/Splunk-Search/How-to-get-full-row-based-on-max/m-p/333486#M99182 <P>Splunkers!</P> <P>I need to solve this problem.<BR /> Basically, starting from a Service Catalogue (having the same AppID linked to more Business Services having potentially different Operational WIndows), i need to select the max Operational Window required (both in term of number and ID).</P> <P>Starting from an inputlookup (so no events as input) i need to select a full row (all columns) based on the max value in a specific column.</P> <P>Current code</P> <PRE><CODE>| inputlookup DOM_ServiceCatalogue | lookup AMAP_ReqAvailability Cluster_Availability as PrimaryWindows OUTPUTNEW ReqWeeklyAvailability as ReqWeekAva, Cluster_Ava_Code as Cluster_Ava_Code | stats max(ReqWeekAva) as ReqWeekAva, values(Cluster_Ava_Code) by CI </CODE></PRE> <P>But values function creates a MV fields, while i just need the "Cluster_Ava_Code" corresponding to the max value selected by stats function.<BR /> Additionally, It might happen to have multiple rows in the Service Catalogue having the same max value for different services supported by the same AppID....</P> <P>How can i do it?</P> <P>Tks!<BR /> Carmine</P> Tue, 29 Sep 2020 17:50:01 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-full-row-based-on-max/m-p/333486#M99182 CarmineCalo 2020-09-29T17:50:01Z https://community.splunk.com/t5/Splunk-Search/How-to-get-full-row-based-on-max/m-p/333487#M99183 <P>Instead of using <CODE>stats</CODE> on line 4, how about instead adding a field called <CODE>max_ReqWeekAva</CODE> to every event, which tracks the max value of <CODE>ReqWeekAva</CODE> by <CODE>CI</CODE> value, and then retaining only the events where <CODE>ReqWeekAva</CODE>=<CODE>max_ReqWeekAva</CODE>? That would look like this:</P> <PRE><CODE>| inputlookup DOM_ServiceCatalogue | lookup AMAP_ReqAvailability Cluster_Availability as PrimaryWindows OUTPUTNEW ReqWeeklyAvailability as ReqWeekAva, Cluster_Ava_Code as Cluster_Ava_Code | eventstats max(ReqWeekAva) as max_ReqWeekAva by CI | where max_ReqWeekAva=ReqWeekAva | stats max(ReqWeekAva) AS ReqWeekAva, max(Cluster_Ava_Code) AS Cluster_AVA_Code BY CI </CODE></PRE> <P>I don't quite understand the second requirement in your post: "Additionally, It might happen to have multiple rows in the Service Catalogue having the same max value for different services supported by the same AppID...." Can you explain that part further?</P> Wed, 24 Jan 2018 19:32:42 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-full-row-based-on-max/m-p/333487#M99183 elliotproebstel 2018-01-24T19:32:42Z https://community.splunk.com/t5/Splunk-Search/How-to-get-full-row-based-on-max/m-p/333488#M99184 <P>Great, it works!</P> <P>The second requirement was related to the need to avoid multivalue fields in the following case</P> <P>CI ReqWeekAva Cluster_Ava_Code<BR /> App1 10 CAC1<BR /> App1 40 CAC2<BR /> App1 30 CAC3<BR /> App1 40 CAC2</P> <P>I mean avoid output like</P> <P>App1 40 CAC1<BR /> 40 CAC1</P> <P>But your solution it's fine, so no issue <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Tue, 29 Sep 2020 17:50:06 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-full-row-based-on-max/m-p/333488#M99184 CarmineCalo 2020-09-29T17:50:06Z