How to filter table results based on an aggregation of a column

[3618475]

I have this search that produces a table with has a column that lists the number of segments to a schedule. The table is shown below

I want to filter on the maximum number of segments (either 2 or 3). This is the query:

   ...search | table purchCostReference, eventType, Time, Segments, Carriers, BillingMethod, Origin, Destination, StopOffLocation |  stats max(Segments) as TotalSegments by purchCostReference, eventType | search TotalSegments = 2  |sort Time

I can use the max method to get the maximum number of segments and use the where to filter on the number of segments that I need but not all of the data is returned, only the columns that I used for the max function and I don't want the column TotalSegments displayed.

I want to return only the rows that have 2 segments and not have an additional column of TotalSegments.

[gcusello]

Hi @3618475,
after your search you can use mvexpand to thansform a multivalue field in separated events so you can use the dc option in stats to count and filter the events with TotalSegments = 2, in othe words, something like this:

...search 
| stats max(Segments) as TotalSegments by purchCostReference, eventType 
| mvexpand TotalSegments 
| stats values(TotalSegments) as TotalSegments dc(TotalSegments) AS dc_TotalSegments by purchCostReference eventType 
| search dc_TotalSegments = 2  

only two notes:

• you don't need the table command before the first stats;

• after a stats command, you have only the fields of your stats, so, in your example, Time isn't a field in your stats, so you cannot sort by it; if you want also Time, you have to add to the stats command also Time, using earliest or latest as option, something like this:

  ...search
  | stats max(Segments) as TotalSegments latest(Time) AS Time BY purchCostReference, eventType
  | mvexpand TotalSegments
  | stats values(TotalSegments) as TotalSegments dc(TotalSegments) AS dc_TotalSegments latest(Time) AS Time BY purchCostReference eventType
  | search dc_TotalSegments = 2
  
  | sort Time

Ciao.
Giuseppe

[3618475]

Thanks for your help.
I was not clear enough in my question.
I want the final output to be the table filtered on Segments of 2 or 3.
If the segments were filtered by Segments = 2, the output should look like the table columns above but with only 2 Segments.
Since the stats command reduces the fields, I probably cant use it

[cboillot]

I know this has been awhile, but have you tried 

| search dc_TotalSegments = 2 OR dc_TotalSegments = 3