Forum Discussion
akshay250692
May 18, 2023Brass Contributor
how to exclude ip along with port
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);
CommonSecurityLog
| 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.
- Clive_WatsonBronze Contributor
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 CommonSecurityLog | where DestinationPort != "445" and DestinationIP !in ('1.1.1.1','2.2.2.2') | summarize arg_max(TimeGenerated,DestinationIP, DestinationPort) by Computer // join to all the data without the excluded records | join kind= rightouter ( CommonSecurityLog | summarize arg_max(TimeGenerated,DestinationIP, DestinationPort) by Computer ) on Computer