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

[ronaldlb80]

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

[woodcock]

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

[ronaldlb80]

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.

[woodcock]

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

[ronaldlb80]

Thank you, Woodcock, that worked.

[DalJeanis · Accepted solution]

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]

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.

[DalJeanis]

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