Splunk Search

How do I do a cidr match/not match in a tstats search?

MonkeyK
Builder

I am trying to get a list of the most common sources and destinations of blocked traffic from the previous day with some exclusions (like not sourced fromauthorized vulnerability scanners). So far I have come up with

Sources:

|tstats summariesonly=t count FROM datamodel=Network_Traffic
WHERE All_Traffic.action!="allowed" All_Traffic.src_ip!=64.39.96.0/20 earliest=-1d@d latest=@d
BY All_Traffic.src_ip
| sort limit=10 -count

But when I do the above, I get results that include an IP address that is in 64.39.96.0/20

I have learned that I can search my results to get rid of the extra source:

|tstats summariesonly=t count FROM datamodel=Network_Traffic
WHERE All_Traffic.action!="allowed" All_Traffic.src_ip!=64.39.96.0/20 earliest=-1d@d latest=@d
BY All_Traffic.src_ip
| search All_Traffic.src_ip!=64.39.96.0/20
| sort limit=10 -count

but this feels inefficient (query something that I don't need and remove it later).

Also, it does not address my next use case: top destination IPs where the source does not include 64.39.96.0/20

|tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
WHERE All_Traffic.action!="allowed" All_Traffic.src_ip!=64.39.96.0/20 earliest=-1d@d latest=@d
BY All_Traffic.dest_ip
| sort limit=10 -count

is there a way to exclude 64.39.96.0/20 directly in the where clause?

0 Karma
1 Solution

woodcock
Esteemed Legend

Actually, natural CIDR filters work in tstats.

Like this:

| tstats count FROM datamodel=Network_Traffic WHERE index=* AND All_Traffic.src="10.0.0.0/8"

And this:

| tstats count WHERE index=* AND host="10.0.0.0/8"

This has been in Splunk for a long time, but maybe not always. It works in all versions of 7.*

View solution in original post

woodcock
Esteemed Legend

Actually, natural CIDR filters work in tstats.

Like this:

| tstats count FROM datamodel=Network_Traffic WHERE index=* AND All_Traffic.src="10.0.0.0/8"

And this:

| tstats count WHERE index=* AND host="10.0.0.0/8"

This has been in Splunk for a long time, but maybe not always. It works in all versions of 7.*

dshpritz
SplunkTrust
SplunkTrust

Just a note that 7.3 adds the ability to have negated CIDR in tstats.

woodcock
Esteemed Legend

I knew that there was something new in 7.something. Thanks!

0 Karma

wryanthomas
Contributor

We've observed that the cidr match functionality in tstats (as described in answer by woodcock -- thanks!) is working for IPv4 addresses ... but not for IPv6 addresses.  Any info on support for cidr match in tstats for IPv6 addresses?

0 Karma

MonkeyK
Builder

I think that I have a way to do the filtering.
By counting on both source and destination, I can then search my results to remove the cidr range, and follow up with a sum on the destinations before sorting them for my top 10

|tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
WHERE All_Traffic.action!="allowed" earliest=-1d@d latest=@d

BY All_Traffic.src_ip All_Traffic.dest_ip
| search All_Traffic.src_ip!=64.39.96.0/20
| chart sum(count) as Count over All_Traffic.dest_ip
| sort limit=10 -Count

Still seems inefficient (although faster than without tstats). Maybe Splunk could do something about that.

0 Karma

isabel_ycourbe
Path Finder

I just ran into your answer since I had the same issue, to slightly improve performance (I think - didn't measure) I did a pre-filter on the tstat using wildcards so I give less results to search, then narrow the results with search (in my case I needed to filter all private IPs) as you suggested

| tstats summariesonly=T 
    count 
    from datamodel=Network_Traffic.All_Traffic 
    where 
        (
            All_Traffic.dest=10.*
            OR All_Traffic.dest=192.168.*
            OR All_Traffic.dest=172.* 
        ) 
        AND All_Traffic.action="allowed" 
        AND 
        (
            All_Traffic.transport="tcp"
            OR All_Traffic.transport="udp"
        ) 
    by All_Traffic.dest
| search All_Traffic.dest=10.0.0.0/8
    OR All_Traffic.dest=192.168.0.0/16
    OR All_Traffic.dest=172.16.0.0/12

In my case the wildcards we almost enough since it works well for /16 and /8, but I still needed to use a CIDR search for 172.16.0.0/12.

MonkeyK
Builder

Good suggestion!

0 Karma

MonkeyK
Builder

I feel a little cheesy accepting my own answer, but I don't think that something better is coming along.

0 Karma

dshpritz
SplunkTrust
SplunkTrust

You shouldn't this is the only correct answer at the moment. Tstats is not CIDR aware for WHERE clauses.

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...