Calculate total for each source IP and append their floor to a search

[kengilmour]

Hi,

I'm trying to get my Firewall logs to combine the total number of traffic generated by specific IP addresses and aggregate the data (working) but also to add an extra field called "Floor" to identify where the IP range is (Not Working)

The following command shows no results.

sourcetype=JuniperFW 
| where cidrmatch("10.0.1.0/24", src)| eval Floor=if(cidrmatch("10.0.1.0/24", src), "1", "Unknown")
| where cidrmatch("10.0.2.0/24", src)| eval Floor=if(cidrmatch("10.0.2.0/24", src), "2", "Unknown")
| where cidrmatch("10.0.3.0/24", src)| eval Floor=if(cidrmatch("10.0.3.0/24", src), "3", "Unknown")
| where cidrmatch("10.0.4.0/24", src)| eval Floor=if(cidrmatch("10.0.4.0/24", src), "4", "Unknown")
| where cidrmatch("10.0.5.0/24", src)| eval Floor=if(cidrmatch("10.0.5.0/24", src), "5", "Unknown")
| where cidrmatch("10.0.6.0/24", src)| eval Floor=if(cidrmatch("10.0.6.0/24", src), "6", "Unknown")
| stats sum(sent) AS TotalSent, sum(rcvd) AS TotalRcvd by src 
| 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 src Floor TotalSentMB TotalRcvdMB TotalMB TotalGB 

If i do the command without the evals then it works but will not show a floor number. What am I missing here?

Thanks!

Ken

[kengilmour]

Hi Ayn,

That's fantastic thanks! Yes it worked... only one error with an unbalanced parenthesis (fixed below)

sourcetype=JuniperFW (src="10.0.1.0/24" OR src="10.0.2.0/24" OR src="10.0.3.0/24" OR src="10.0.4.0/24" OR src="10.0.5.0/24" OR src="10.0.6.0/24") | eval Floor=case(cidrmatch("10.0.1.0/24", src), "1", cidrmatch("10.0.2.0/24", src), "2", cidrmatch("10.0.3.0/24", src), "3", cidrmatch("10.0.4.0/24", src), "4", cidrmatch("10.0.5.0/24", src), "5", cidrmatch("10.0.6.0/24", src), "6", 1=1, "Unknown")
| stats sum(sent) AS TotalSent, sum(rcvd) AS TotalRcvd by src 
| 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 src Floor TotalSentMB TotalRcvdMB TotalMB TotalGB

Thanks very much!

Ken

[kengilmour]

Got it thanks 🙂

[Ayn]

In your base search, just add something like:

... AND NOT (dst="10.0.0.0/8" OR dst="172.16.0.0/12")

[kengilmour]

Hi Ayn, actually, just wondering; if i wanted to negate a cidrmatch how would I do that? So i want to negate anything in this list where "dst" does not match 10.0.0.0/8 or 172.16.0.0/12 so that it will show only the bandwidth outside of the network and not over the VPN.

[Ayn]

Ah, gotcha! Editing my answer as well so that there's no syntax error in it. Great that it worked!

[Ayn · Accepted solution]

Your search is pretty inefficient right now. What you do now is to first read in ALL the data having sourcetype "JuniperFW" before sending it into the rest of the search pipeline. Then you have a row of where operators who will, in turn, filter the data further. It seems you've got the impression that the where and eval statement on each line in your search somehow create a specific context? Like, "where this condition is true, do this and don't continue with the rest of the where/eval statements" - well, if that's the case, that's not how it works. The where operator simply filters results based on some condition, and the eval operator assigns a value to a field. The data the moves on into the pipeline, so if you had a src belonging to 10.0.1.0/24, the first eval command would match that and say that Floor is "1" BUT after that you arrive at the second eval command which will test if src belongs to "10.0.2.0/24" which it doesn't, and Floor will then be rewritten to be "Unknown".

This is a more efficient search that should in theory (disclaimer: I haven't actually tested it) do what you want. It uses one eval statement with a case function, which will behave like one would want it to - i.e. look at the src and compare it to the different subnets, giving Floor the value defined for the first match. As a default last case statement there is "1=1" which is of course always true, and will make case default to "Unknown" if none of the previous cidrmatch statements returned true.

sourcetype=JuniperFW (src="10.0.1.0/24" OR src="10.0.2.0/24" OR src="10.0.3.0/24" OR src="10.0.4.0/24" OR src="10.0.5.0/24" OR src="10.0.6.0/24") | eval Floor=case(cidrmatch("10.0.1.0/24", src), "1", cidrmatch("10.0.2.0/24", src), "2", cidrmatch("10.0.3.0/24", src), "3", cidrmatch("10.0.4.0/24", src), "4", cidrmatch("10.0.5.0/24", src), "5", cidrmatch("10.0.6.0/24", src), "6", 1=1, "Unknown")
| stats sum(sent) AS TotalSent, sum(rcvd) AS TotalRcvd by src 
| 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 src Floor TotalSentMB TotalRcvdMB TotalMB TotalGB