topic Re: Calculate the percentage of IP CIDR in Splunk Search

How to calculate the percentage of IP CIDR?

Lye — Fri, 07 Oct 2022 14:17:48 GMT

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)

Re: How to calculate the percentage of IP CIDR?

bowesmana — Sun, 09 Oct 2022 22:44:01 GMT

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)

Re: Calculate the percentage of IP CIDR

johnhuang — Fri, 07 Oct 2022 03:29:26 GMT

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

Re: Calculate the percentage of IP CIDR

yuanliu — Fri, 07 Oct 2022 03:47:27 GMT

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

Re: Calculate the percentage of IP CIDR

Lye — Sat, 08 Oct 2022 05:18:49 GMT

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

Re: Calculate the percentage of IP CIDR

Lye — Sat, 08 Oct 2022 05:22:19 GMT

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

Re: Calculate the percentage of IP CIDR

Lye — Sat, 08 Oct 2022 05:25:29 GMT

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

Re: Calculate the percentage of IP CIDR

yuanliu — Sat, 08 Oct 2022 06:00:24 GMT

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

Re: Calculate the percentage of IP CIDR

Lye — Sat, 08 Oct 2022 20:03:56 GMT

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

Re: Calculate the percentage of IP CIDR

yuanliu — Sat, 08 Oct 2022 22:16:10 GMT

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

Re: Calculate the percentage of IP CIDR

johnhuang — Mon, 10 Oct 2022 16:22:27 GMT

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.

Re: Calculate the percentage of IP CIDR

Lye — Mon, 10 Oct 2022 21:50:25 GMT

@johnhuang , yes it's the correct fieldname.

Re: How to calculate the percentage of IP CIDR?

Lye — Mon, 10 Oct 2022 22:09:12 GMT

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.