Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Getting Data In

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Splunk Administration
- :
- Getting Data In
- :
- Re: Calculate total for each source IP and append ...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

kengilmour

Path Finder

01-17-2013
04:01 AM

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

1 Solution

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Ayn

Legend

01-17-2013
04:20 AM

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
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

kengilmour

Path Finder

01-17-2013
04:31 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Ayn

Legend

01-17-2013
04:33 AM

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

Highlighted
##

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

kengilmour

Path Finder

01-17-2013
04:44 AM

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Ayn

Legend

01-17-2013
04:46 AM

In your base search, just add something like:

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

kengilmour

Path Finder

01-17-2013
04:49 AM

Got it thanks 🙂