Splunk Search

How to edit my search to find the total bandwidth by office subnet?

ronaldlb80
Engager

Hi,

We have MPLS connection and all our offices are getting the internet from our main office.

What I want to see in a report total Internet Traffic by CIDR

I have tried below but it would not show me the total bandwidth usage

index=* (srcip="10.1.1.0/24" OR srcip="10.1.2.0/24" OR srcip="10.3.0.0/24" OR srcip="10.4.0.0/22" OR srcip="10.5.0.0/22" OR srcip="10.6.0.0/22") | eval Floor=case(cidrmatch("10.1.1.0/24", srcip), "1", cidrmatch("10.1.2.0/24", srcip), "2", cidrmatch("10.3.0.0/24", srcip), "3", cidrmatch("10.4.0.0/22", srcip), "4", cidrmatch("10.5.0.0/22", srcip), "5", cidrmatch("10.6.0.0/22", srcip), "6", 1=1, "Unknown") | stats sum(sentbyte) AS TotalSent, sum(rcvdbyte) AS TotalRcvd by srcip  | eval TotalSentMB=round(TotalSent/1024/1024,2) | eval TotalRcvdMB=round(TotalRcvd/1024/1024,2) | eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2) | eval TotalGB=round((TotalSent+TotalRcvd)/1024/1024/1024,2) | table srcip TotalMB TotalGB
0 Karma
1 Solution

DalJeanis
Legend

Your query should have been giving you TotalMB and TotalGB summed up by individual IP. If the below doesn't work, then you'll need to tell us clearly what it IS doing.

Try this -

index=* (srcip="10.1.1.0/24" OR srcip="10.1.2.0/24" OR srcip="10.3.0.0/24" 
      OR srcip="10.4.0.0/22" OR srcip="10.5.0.0/22" OR srcip="10.6.0.0/22") 
| eval CIDR=case(cidrmatch("10.1.1.0/24", srcip), "10.1.1.0/24", 
                 cidrmatch("10.1.2.0/24", srcip), "10.1.2.0/24", 
                 cidrmatch("10.3.0.0/24", srcip), "10.3.0.0/24", 
                 cidrmatch("10.4.0.0/22", srcip), "10.4.0.0/22", 
                 cidrmatch("10.5.0.0/22", srcip), "10.5.0.0/22", 
                 cidrmatch("10.6.0.0/22", srcip), "10.6.0.0/22", 
                 true(), "Unknown CIDR") 
| stats sum(sentbyte) AS TotalSent, sum(rcvdbyte) AS TotalRcvd by CIDR 
| eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2) 
| eval TotalGB=round(TotalMB/1024,2) 
| table CIDR TotalMB TotalGB

All I've done is get some stuff you weren't actually using out of the way and straighten up the formatting.

View solution in original post

woodcock
Esteemed Legend

Like this:

index=* (srcip="10.1.1.0/24" OR srcip="10.1.2.0/24" OR srcip="10.3.0.0/24" OR srcip="10.4.0.0/22" OR srcip="10.5.0.0/22" OR srcip="10.6.0.0/22")
| eval Floor=case(cidrmatch("10.1.1.0/24", srcip), "1",
                  cidrmatch("10.1.2.0/24", srcip), "2",
                  cidrmatch("10.3.0.0/24", srcip), "3",
                  cidrmatch("10.4.0.0/22", srcip), "4",
                  cidrmatch("10.5.0.0/22", srcip), "5",
                  cidrmatch("10.6.0.0/22", srcip), "6",
                                           true(), "Unknown")
| stats sum(sentbyte) AS TotalSent sum(rcvdbyte) AS TotalRcvd BY Floor
| eval TotalSentMB=round(TotalSent/1024/1024,2)
| eval TotalRcvdMB=round(TotalRcvd/1024/1024,2)
| eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2)
| eval TotalGB=round((TotalSent+TotalRcvd)/1024/1024/1024,2)
| table Floor TotalMB TotalGB
0 Karma

ronaldlb80
Engager

Thank You for your help,

This works but when I try to get service included it doesn't show up the services by MB.

 index=* (srcip="10.1.1.0/24" OR srcip="10.1.2.0/24" OR srcip="10.3.0.0/24" 
       OR srcip="10.4.0.0/22" OR srcip="10.5.0.0/22" OR srcip="10.6.0.0/22") 
 | eval CIDR=case(cidrmatch("10.1.1.0/24", srcip), "10.1.1.0/24", 
                  cidrmatch("10.1.2.0/24", srcip), "10.1.2.0/24", 
                  cidrmatch("10.3.0.0/24", srcip), "10.3.0.0/24", 
                  cidrmatch("10.4.0.0/22", srcip), "10.4.0.0/22", 
                  cidrmatch("10.5.0.0/22", srcip), "10.5.0.0/22", 
                  cidrmatch("10.6.0.0/22", srcip), "10.6.0.0/22", 
                  true(), "Unknown CIDR") 
 | stats **values(service)** sum(sentbyte) AS TotalSent, sum(rcvdbyte) AS TotalRcvd by CIDR 
 | eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2) 
 | eval TotalGB=round(TotalMB/1024,2) 
 | table CIDR **Service** TotalMB TotalGB

the result is empty service.

Any help on this.

0 Karma

woodcock
Esteemed Legend

Change values(service) to values(service) AS Service.

0 Karma

ronaldlb80
Engager

Thank you, Woodcock, that worked.

0 Karma

DalJeanis
Legend

Your query should have been giving you TotalMB and TotalGB summed up by individual IP. If the below doesn't work, then you'll need to tell us clearly what it IS doing.

Try this -

index=* (srcip="10.1.1.0/24" OR srcip="10.1.2.0/24" OR srcip="10.3.0.0/24" 
      OR srcip="10.4.0.0/22" OR srcip="10.5.0.0/22" OR srcip="10.6.0.0/22") 
| eval CIDR=case(cidrmatch("10.1.1.0/24", srcip), "10.1.1.0/24", 
                 cidrmatch("10.1.2.0/24", srcip), "10.1.2.0/24", 
                 cidrmatch("10.3.0.0/24", srcip), "10.3.0.0/24", 
                 cidrmatch("10.4.0.0/22", srcip), "10.4.0.0/22", 
                 cidrmatch("10.5.0.0/22", srcip), "10.5.0.0/22", 
                 cidrmatch("10.6.0.0/22", srcip), "10.6.0.0/22", 
                 true(), "Unknown CIDR") 
| stats sum(sentbyte) AS TotalSent, sum(rcvdbyte) AS TotalRcvd by CIDR 
| eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2) 
| eval TotalGB=round(TotalMB/1024,2) 
| table CIDR TotalMB TotalGB

All I've done is get some stuff you weren't actually using out of the way and straighten up the formatting.

ronaldlb80
Engager

Hi DalJeanis,

Thank you for clearing up the command now it shows the total usage by CIDR.

There is one thing is not showing is the internet traffic by CIDR, it gives me all the traffic which was transferred over the MPLS, is there a way I can filter only the Internet traffic out of this?

Thank You.

0 Karma

DalJeanis
Legend

If that is your full list of internal IPs, then I would expect that you just have to do the same tests on the destination ip. The following code assumes that the field name is dstip

 index=* 
 (srcip="10.1.1.0/24"  OR  srcip="10.1.2.0/24"  OR  srcip="10.3.0.0/24"  OR 
  srcip="10.4.0.0/22"  OR  srcip="10.5.0.0/22"  OR  srcip="10.6.0.0/22") 
 (dstip!="10.1.1.0/24" AND dstcip!="10.1.2.0/24" AND dstip="10.3.0.0/24" AND 
  dstip!="10.4.0.0/22" AND dstip!="10.5.0.0/22" AND dstip!="10.6.0.0/22") 
0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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