How to calculate the percentage of IP CIDR?

[Lye]

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)

[Lye]

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.

[johnhuang]

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]

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

[johnhuang]

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.

[Lye]

@johnhuang , yes it's  the correct fieldname.

[bowesmana]

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]

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

[yuanliu]

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]

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

[yuanliu]

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_range	count
10.12.143.0/24	2
10.12.6.0/24	1
172.0.58.0/24	1
192.168.5.0/24	3

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

@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_range	clientip	count	perc	percentage
1.1.1.1- 1.1.1.255	1.1.1.9	1	4	25
1.3.146.1- 1.3.146.255	1.3.146.253	1	4	25
1.21.112.1 - 1.21.112.255	1.21.112.32	1	4	25
3.6.71.1 -3.6.71.255	3.6.71.70	1	4	25

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

clientip	count
1.1.1.9	800
1.3.146.253	75
1.21.112.32	44
3.6.71.70	52

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.

[yuanliu]

@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)