Splunk Search

How to calculate the percentage of IP CIDR?

Lye
Path Finder

Hi, 
Kindly assist me as I am not getting the results I anticipate.
I wish to have a table like this

ClientIP Count Percentage
1.1.1.1 - 1.1.1.255 50 50%
2.1.1.0 - 2.1.1.255 25 25%
3.1.1.0 - 3.1.1.255 25 25%
Total 100 100


Presently my query does NOT have the CIDR as I wished . It spits out individual IPs but it would be nice to have the IPs in the same CIDR range grouped in one column. That way I have a nice looking table.

I used this query to get individual percentage but not happy with the results. I would really appreciate any help.

index=* sourcetype=*
| stats count by clientip
| eventstats sum(count) as perc
| eval percentage = round(count*100/perc,2)

Labels (1)
Tags (2)
0 Karma

Lye
Path Finder

I used these 2 queries below. Both gave me results (events and statistics) although a slight difference in values. 

index=* sourcetype=*
| rex field=clientip "(?<ip_subnet>\d+\.\d+\.\d+)"
| eval ip_subnet_range=".0 - ".ip_subnet.".255"
| eval ip_subnet=ip_subnet.".0/24"
| stats count by ip_subnet
| eventstats sum(count) as perc
| eval percentage =round(count*100/perc,2)


And 

index=* sourcetype=*
| rex field=clientip "(?<cidr_range>\d+\.\d+\.\d+)"
| eval cidr_range=cidr_range.".0 - ".cidr_range.".255"
| stats count by cidr_range
| eventstats sum(count) as perc
| eval percentage = round(count*100/perc,2)


Thank you so much to  @bowesmana , @johnhuang , @yuanliu . I really appreciate all your help.

0 Karma

johnhuang
Motivator
index=* sourcetype=*
| rex field=clientip "(?<ip_subnet>\d+\.\d+\.\d+)\.\d+"
| eval ip_subnet_range=ip_subnet.".0 - ".ip_subnet.".255"
| eval ip_subnet_cidr=ip_subnet.".0/24"
| stats dc(clientip) AS ip_count BY ip_subnet_cidr ip_subnet_range
| eventstats sum(ip_count) AS total_ip_count
| eval percentage=ROUND((ip_count/total_ip_count)*100, 2)." %"
| addcoltotals labelfield=ip_subnet_cidr
| eval percentage=IF(ip_subnet_cidr="Total", "100.00 %", percentage)
| table ip_subnet_cidr ip_subnet_range ip_count percentage

Lye
Path Finder

@johnhuang , Thanks for your response. For some reasons, I couldn't get statistics values or in tabular form. I did get events and it still gave me individual IPs. I am not sure what I did wrong.

0 Karma

johnhuang
Motivator

Is the "clientip" the correct fieldname for the client ip? If not, either rename the field to "clientip" or modify the query to use the correct fieldname.

0 Karma

Lye
Path Finder

@johnhuang , yes it's  the correct fieldname.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

You need to convert the client_ip to a CIDR range, then count by that range, e.g.

 

... your search ...
| rex field=clientip "(?<cidr_range>\d+\.\d+\.\d+)"
| eval cidr_range=cidr_range.".0/24"
| stats count by cidr_range
...

 

That will then make A.B.C.0/24 - if you want to make it A.B.C.0-A.B.C.255 then just change the eval statement

(Edited - to change CIDR range from 8 to 24 as per @yuanliu comment)

Lye
Path Finder

@bowesmana , Thank you for responding. It still yielded individual IPs and no table. 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

A versatile alternative to rex is  ipmask function.

| eval cidr_range = ipmask("255.255.255.0", clientip)
| stats count by cidr_range
| eventstats sum(count) as total
| eval percent = 100 * count / total

 

Lye
Path Finder

@yuanliu, Thanks for your post. I got an error message in the eval command and no results. 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Just noticed that ipmask is only available since Splunk 9.

I don't understand why @bowesmana's solution shouldn't work.  It definitely outputs what you wanted with the following data

clientip
10.12.143.5
192.168.5.250
172.0.58.52
192.168.5.13
10.12.6.8
10.12.143.82
192.168.5.2

Using the the exact formula from @bowesmana's post except the correct CIDR mask of /24:

 

| makeresults
| eval clientip = mvappend("10.12.143.5","192.168.5.250","172.0.58.52","192.168.5.13","10.12.6.8","10.12.143.82","192.168.5.2")
| mvexpand clientip
``` ^^^ data emulation ```

| rex field=clientip "(?<cidr_range>\d+\.\d+\.\d+)"
| eval cidr_range=cidr_range.".0/24"
| stats count by cidr_range

 

I get the correct count.

cidr_rangecount
10.12.143.0/242
10.12.6.0/241
172.0.58.0/241
192.168.5.0/243

 

| rex field=clientip "(?<cidr_range>\d+\.\d+\.\d+)"
| eval cidr_range=cidr_range.".1 - ".cidr_range.".255"
| stats count by cidr_range​

 

Is this what you needed?  The only drawback here is that this only works with /24, /16, and /8.

If you really wanted to use a range notation, you can do

 

| rex field=clientip "(?<cidr_range>\d+\.\d+\.\d+)"
| eval cidr_range=cidr_range.".1 - "."cidr_range.".255"
| stats count by cidr_range

 

Lye
Path Finder

@yuanliu , I tried the query you sent in addition to  the one from @bowesmana . Yes, I got results. A table included.
1st question: since I have over 100 IPs, do I have to individually add them here like you mentioned below?

| eval clientip = mvappend("10.12.143.5","192.168.5.250","172.0.58.52","192.168.5.13","10.12.6.8","10.12.143.82","192.168.5.2")

This was my query with pseudo IPs

| makeresults
index=* sourcetype=*
| eval clientip = mvappend("1.1.1.9","1.3.146.253","1.21.112.32","3.6.71.70")
| mvexpand clientip
| rex field=src_ip "(?<cidr_range>\d+\.\d+\.\d+)"
| eval cidr_range=cidr_range.".1 - ".cidr_range.".255"
| stats count by cidr_range, clientip
| eventstats sum(count) as perc
| eval percentage = round(count*100/perc,2)

I get a table that looks like this

cidr_rangeclientipcountpercpercentage
1.1.1.1- 1.1.1.2551.1.1.91425
1.3.146.1- 1.3.146.2551.3.146.2531425
1.21.112.1 - 1.21.112.2551.21.112.321425
3.6.71.1 -3.6.71.2553.6.71.701425



But when I use this query

index=* clientip="*"
| stats count by clientip

to see/get all the list and count of the IPs individually I get something  roughly like this

clientipcount
1.1.1.9800
1.3.146.25375
1.21.112.32
44
3.6.71.7052



In the logs, I have 1.1.1.2, 1.1.1.3, 1.1.1.4, 1.1.1.5 with their respective counts. It would be nice since they are in the same range to have them counted together in one column.

That is I was hoping I could get the appropriate count for IPs within a cidr_range that way I can have a concise table instead of individual IPs and their count. Considering that I need to have a percentage of each cidr_range.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

@Lye makeresults is just a common method to emulate data.  It is a generating command, thus cannot be combined with other generating commands such as your normal search.  Sorry for not explaining this clearly.  Just apply what @bowesmana illustrated to your actual search, e.g.,

index=* clientip=*
| rex field=clientip "(?<cidr_range>\d+\.\d+\.\d+)"
| eval cidr_range=cidr_range.".1 - ".cidr_range.".255"
| stats count by cidr_range, clientip
| eventstats sum(count) as perc
| eval percentage = round(count*100/perc,2)

 

0 Karma
Get Updates on the Splunk Community!

Splunk Federated Analytics for Amazon Security Lake

Thursday, November 21, 2024  |  11AM PT / 2PM ET Register Now Join our session to see the technical ...

Splunk With AppDynamics - Meet the New IT (And Engineering) Couple

Wednesday, November 20, 2024  |  10AM PT / 1PM ET Register Now Join us in this session to learn all about ...

Building a Self-Service and Scalable Observability Practice

Thursday, November 14, 2024  |  11AM PT / 2PM ET Register Now Join us in this session and learn how Splunk ...