Splunk Search

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

3618475
Engager

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 belowalt text

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.

Labels (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

3618475
Engager

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

0 Karma

cboillot
Contributor

I know this has been awhile, but have you tried 

| search dc_TotalSegments = 2 OR dc_TotalSegments = 3

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...