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

[nychawk]

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

[evelopers · Accepted solution]

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"

[nychawk]

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?

[evelopers]

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"