How to sort by numerical, not lexigraphical order (using a stats command or equivalent)?

[hulahoop]

Here's what we are trying to do with our Juniper Netscreen Firewall data for outbound denied traffic:

1. Find the top 3 Protocol and Dst_Port combination where Action="pckt dropped" (using summarized data)
2. For each combination, get the top hosts
3. Sort the hosts by event count

We can get as far as #2 using subsearch, eval and the stats values function:

index=summary search_name="MySummarySearch"
    [search index=summary search_name="MySummarySearch"
    | top Dst_Port by Protocol
    | sort - count
    | head 3
    | fields + Dst_Port,Protocol]
| eval foo=Protocol+" - "+Dst_Port
| top foo by Src_Addr
| eval bar="("+count+") "+Src_Addr
| stats values(bar) as Hosts sum(count) as Total by foo
| rename foo as "Protocol & Dest Port"
| sort - Total

The output looks something like this:

Protocol & Dest Port        Hosts        Total
-------------------- ------------------- -----
tcp - 80             (1) 10.15.12.111       54
                     (1) 10.15.125.111        
                     (1) 10.2.64.111         
                     (1) 10.2.64.222         
                     (10) 10.15.171.111      
                     (13) 10.15.171.222      
                     (18) 10.5.40.111        
                     (2) 10.15.124.111       
                     (2) 10.20.176.111       
                     (2) 10.9.72.111          
                     (3) 10.20.8.111          
-------------------- ------------------- -----
udp - 53             (17) 10.5.4.111        41
                     (5) 10.15.12.111       
                     (5) 10.5.4.111         
                     (7) 10.15.12.111        
                     (7) 10.15.12.111       
-------------------- ------------------- -----
tcp - 443            (14) 10.15.1.111       29
                     (15) 10.15.1.222      

How to get the Hosts column to sort by the numerical event count shown above in parens? We like the grouping provided by the stats values() function, but it sorts lexigraphically. 😞

[Stephen_Sorkin]

You can achieve this by sorting the intermediate results by the "count" field and use list() instead of values() to preserve order. Here's the search:

index=summary search_name="MySummarySearch" [search index=summary search_name="MySummarySearch" | top Dst_Port by Protocol | sort - count | head 3 | fields + Dst_Port,Protocol] | eval foo=Protocol+" - "+Dst_Port | top foo by Src_Addr | eval bar="("+count+") "+Src_Addr | sort - count | stats list(bar) as Hosts sum(count) as Total by foo | rename foo as "Protocol & Dest Port" | sort - Total

[Simeon]

I would try a secondary sort:

sort -Total, host

[sideview]

I could be wrong but i think you want another "| stats count by foo Src_Addr" injected just after evalling the foo, and its right after that, that you'd want the 2-level sort. Might do the trick.

[hulahoop]

This will not sort the count per host as presented by the values() function, but rather sorts by the Total count by protocol. The sort command does not see into the values list unfortunately.