Forum Discussion

akshay250692's avatar
akshay250692
Brass Contributor
May 18, 2023

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_Watson's avatar
    Clive_Watson
    Bronze Contributor

    akshay250692 

     

    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

     

Resources