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!

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...