Splunk Search

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

hulahoop
Splunk Employee
Splunk Employee

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
Splunk Employee
Splunk Employee

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
Splunk Employee
Splunk Employee

I would try a secondary sort:

sort -Total, host

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

hulahoop
Splunk Employee
Splunk Employee

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.

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 ...