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.
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:
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
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
I know this has been awhile, but have you tried
| search dc_TotalSegments = 2 OR dc_TotalSegments = 3