how to exclude ip along with port

Brass Contributor

Hi Team,


I have created one rule and want to exclude 2 destination ip  along with port destination port 445. how it will write ?

Below is my query

let deviceIP = (_GetWatchlist('qwe')
| project SearchKey);
| where DeviceVendor =~ "Palo Alto Networks"
and DeviceProduct =~ "PAN-OS"
and Activity =~ "THREAT"
| where Computer !in (deviceIP)
and DeviceCustomString4 == "GP_VPN" or DeviceCustomString4 == "GP_partnervpn"
and DeviceAction =~ "alert"
| where DeviceEventClassID != "url" and LogSeverity !in ("1", "2", "3")
| where DestinationPort != "L" and DestinationIP !in ("x.y.z.x", "q.w.e.r", "a.s.d.f")
| parse kind=regex flags=U AdditionalExtensions with * "cat=" Category ';'*
| project TimeGenerated, Subtype = DeviceEventClassID, SourceIP, SourceTranslatedAddress, Source_Ports=strcat(SourcePort),SourceUserName, DestinationIP, DestinationTranslatedAddress, DestinationPorts = strcat(DestinationPort), DestinationUserName,Protocol,ApplicationProtocol, RequestURL, Rules=DeviceCustomString1, Category, FirewallNames = Computer, SourceZone = DeviceCustomString4, DestinationZone = DeviceCustomString5 


now i have to exclude 2 destination ip (x.d.r.t, c.f.t.y) with destination port (445)

i wrote in this way but not worked.

| where DestinationPort != "445" and DestinationIP !in (x.d.r.t, c.f.t.y)


above line exclude all 445 related logs with any ip. but i just want exclude (x.d.r.t, c.f.t.y) with only 445.

if ip (x.d.r.t, c.f.t.y) come with any other port logs should come. or except ip (x.d.r.t, c.f.t.y) all logs come in 445 destination port.


1 Reply



You'll probably need to join the data - to build a list of the IPs with that port, then join it to the IP that dont - something like this (which is an untested example) 

// Exclude these 
| where DestinationPort != "445" and DestinationIP !in ('','')
| summarize arg_max(TimeGenerated,DestinationIP, DestinationPort) by Computer
// join to all the data without the excluded records
| join kind= rightouter   
    | summarize arg_max(TimeGenerated,DestinationIP, DestinationPort) by Computer
) on Computer