All Apps and Add-ons

Netflow Analytics for Splunk: How to create a report by source IP to non-private dest IP - by application and count?

Communicator

I already have a report showing outbound traffic to non-private IP addresses. I now need a report that shows the same, however, only for the following applications:
imap
pop
smtp
rdp

The issue is that there are several variants for each of these, i.e. pop has pop3, pop4, imap has imaps, etc.

How can I show this data, where each source IP and dest IP by application and count is shown separately?

By the way, awesome app, it's become my swiss-army knife!

-mi

0 Karma
1 Solution

Explorer

For this purpose the query from the table in the Traffic by Protocol and Port dashboard can be reused.

1) to filter out outbound traffic to private ip addresses:

| where NOT ( cidrmatch("10.0.0.0/8",dest_ip) OR cidrmatch("172.16.0.0/12",dest_ip) OR cidrmatch("192.168.0.0/16",dest_ip) OR cidrmatch("169.254.0.0/16",dest_ip) )

2) to filter applications:

| search ( dest_port_string="(pop)" OR dest_port_string="(imap)" OR dest_port_string="(smtp)" OR dest_port_string="(rdp)")

it can be extended as needed.

After removing superfluous filters, and adding the two new above the final query would be:

`netflow_search_rule_20067` 
| where  NOT ( cidrmatch("10.0.0.0/8",dest_ip) 
    OR cidrmatch("172.16.0.0/12",dest_ip) 
    OR cidrmatch("192.168.0.0/16",dest_ip) 
    OR cidrmatch("169.254.0.0/16",dest_ip) )      
| `fix_src_ip_mapping`                 
| `fix_dest_ip_mapping` 
| lookup protocol_lookup protocol AS protocol 
| `format_port_column(dest_port_string, dest_port)`    
| search ( dest_port_string="*(pop*)" 
    OR   dest_port_string="*(imap*)" 
    OR dest_port_string="*(smtp)" 
    OR  dest_port_string="*(rdp)")                         
| `sampling(bytes_in)`                  
| `sampling(packets_in)`                  
| `sampling(flow_count)`                  
| stats sum(bytes_in) AS TrafficAmount  sum(packets_in) AS PacketsAmount                          sum(flow_count) AS Connections                          max(_time) as max_time                         min(_time) as min_time                         by exp_ip src_ip dest_ip dest_port_string     
| `pct_of_total(pct, "20067", TrafficAmount)`                 
| `default_preparation_for_comma_formatted_table`                 
| table exp_ip_name src_ip dest_ip dest_port_string  "Average Bits/s" "Total Traffic Bytes" pct "Average Packets/s" "Total Packets" "Total Connections"                 
| rename exp_ip_name as "Device"                 
| rename dest_port_string as "Destination Port"                 
| rename pct as "% of Total"

View solution in original post

Explorer

For this purpose the query from the table in the Traffic by Protocol and Port dashboard can be reused.

1) to filter out outbound traffic to private ip addresses:

| where NOT ( cidrmatch("10.0.0.0/8",dest_ip) OR cidrmatch("172.16.0.0/12",dest_ip) OR cidrmatch("192.168.0.0/16",dest_ip) OR cidrmatch("169.254.0.0/16",dest_ip) )

2) to filter applications:

| search ( dest_port_string="(pop)" OR dest_port_string="(imap)" OR dest_port_string="(smtp)" OR dest_port_string="(rdp)")

it can be extended as needed.

After removing superfluous filters, and adding the two new above the final query would be:

`netflow_search_rule_20067` 
| where  NOT ( cidrmatch("10.0.0.0/8",dest_ip) 
    OR cidrmatch("172.16.0.0/12",dest_ip) 
    OR cidrmatch("192.168.0.0/16",dest_ip) 
    OR cidrmatch("169.254.0.0/16",dest_ip) )      
| `fix_src_ip_mapping`                 
| `fix_dest_ip_mapping` 
| lookup protocol_lookup protocol AS protocol 
| `format_port_column(dest_port_string, dest_port)`    
| search ( dest_port_string="*(pop*)" 
    OR   dest_port_string="*(imap*)" 
    OR dest_port_string="*(smtp)" 
    OR  dest_port_string="*(rdp)")                         
| `sampling(bytes_in)`                  
| `sampling(packets_in)`                  
| `sampling(flow_count)`                  
| stats sum(bytes_in) AS TrafficAmount  sum(packets_in) AS PacketsAmount                          sum(flow_count) AS Connections                          max(_time) as max_time                         min(_time) as min_time                         by exp_ip src_ip dest_ip dest_port_string     
| `pct_of_total(pct, "20067", TrafficAmount)`                 
| `default_preparation_for_comma_formatted_table`                 
| table exp_ip_name src_ip dest_ip dest_port_string  "Average Bits/s" "Total Traffic Bytes" pct "Average Packets/s" "Total Packets" "Total Connections"                 
| rename exp_ip_name as "Device"                 
| rename dest_port_string as "Destination Port"                 
| rename pct as "% of Total"

View solution in original post

Communicator

That was the fastest reply I've ever seen, thank you.
This report is exactly what I was looking for.

Is there any way to negate my internally allowed servers (internal SMTP servers, DNS servers, etc), presumably from a file, or ES?

0 Karma

Explorer

Assuming that you would like to create a CSV file with IP addresses to be excluded from search results :

1)As the first step a list of ip addresses should be created as a csv file, in this case with only one column, for example

ip
10.0.1.1
192.168.0.23
192.168.0.24

It can be uploaded to Splunk using

Settings >> Lookups >> Lookup table files >> New

2) it can be registered as a lookup using

Settings >> Lookups >> Lookup definitions >> New

there the previous csv file should be selected and named, for example as src_ip_blacklist

3) the following can be added to the query, to filter out the ip addresses

NOT [|inputlookup src_ip_blacklist | rename ip AS src_ip | fields src_ip] 

The updated query would be look like:

     `netflow_search_rule_20067` 
         NOT [|inputlookup src_ip_blacklist 
            | rename ip AS src_ip 
            | fields src_ip] 
     | where  NOT ( cidrmatch("10.0.0.0/8",dest_ip) 
         OR cidrmatch("172.16.0.0/12",dest_ip) 
         OR cidrmatch("192.168.0.0/16",dest_ip) 
         OR cidrmatch("169.254.0.0/16",dest_ip) )      
     | `fix_src_ip_mapping`                 
     | `fix_dest_ip_mapping` 
     | lookup protocol_lookup protocol AS protocol 
     | `format_port_column(dest_port_string, dest_port)`    
     | search ( dest_port_string="*(pop*)" 
         OR   dest_port_string="*(imap*)" 
         OR dest_port_string="*(smtp)" 
         OR  dest_port_string="*(rdp)")                         
     | `sampling(bytes_in)`                  
     | `sampling(packets_in)`                  
     | `sampling(flow_count)`                  
     | stats sum(bytes_in) AS TrafficAmount  sum(packets_in) AS PacketsAmount                          sum(flow_count) AS Connections                          max(_time) as max_time                         min(_time) as min_time                         by exp_ip src_ip dest_ip dest_port_string     
     | `pct_of_total(pct, "20067", TrafficAmount)`                 
     | `default_preparation_for_comma_formatted_table`                 
     | table exp_ip_name src_ip dest_ip dest_port_string  "Average Bits/s" "Total Traffic Bytes" pct "Average Packets/s" "Total Packets" "Total Connections"                 
     | rename exp_ip_name as "Device"                 
     | rename dest_port_string as "Destination Port"                 
     | rename pct as "% of Total"
0 Karma