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 more
