Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community
SOLVED

KQL query not showing sourec country info

Brass Contributor

Hi Team,

 

I created one query from commonsecurity table it showing destination country info but not source country. Please help me to find out source country info also. source country column getting empty but destnation column getting info like ip and country name

 

CommonSecurityLog
| where DeviceProduct has 'PAN-OS' and DeviceVendor =~ 'Palo Alto Networks' and Activity =~ 'THREAT'
| where TimeGenerated >= ago(1d)
| extend IsLateral = iif(((ipv4_is_private(SourceIP) == true) and (ipv4_is_private(DestinationIP)== true)) , 'true', 'false')
    | where IsLateral == 'false'
    | extend IsOutbound = iif(((ipv4_is_private(SourceIP) == true) and (ipv4_is_private(DestinationIP) == false)) , 'true', 'false'),
             IsInbound = iif(ipv4_is_private(SourceIP) == false, 'true', 'false')
    | extend TrafficDirection = iif((IsOutbound == 'true'), "Outbound", "Inbound")
| extend Simplified_Firewall_Action = iif((DeviceAction in ("allow", "alert", "block-continue", "continue")), 'Allowed', 'Blocked')
| parse  kind=regex flags=U AdditionalExtensions with * "DstLocation=" DestinationCountry ';' * "SrcLocation=" SourceCountry
| summarize count() by DeviceEventClassID, LogSeverity, Detailed_Firewall_Action = DeviceAction, Simplified_Firewall_Action, TrafficDirection, SourceCountry, DestinationCountry
| project Date = now(-1d), Subtype = DeviceEventClassID, Severity = LogSeverity, Detailed_Firewall_Action, Simplified_Firewall_Action, TrafficDirection, SourceCountry, DestinationCountry, Count=count_

2 Replies
best response confirmed by akshay250692 (Brass Contributor)
Solution
Generally if the Vendor doesn't supply the data you would have to try and look it up in another Table.

By the way you could have used ASIM for this (simple example):

_Im_NetworkSession_PaloAltoCEFV06(starttime=ago(1d))
// or use _ASim_NetworkSession if you dont need a parameter https://docs.microsoft.com/en-us/azure/sentinel/network-normalization-schema
| where Activity =~"Threat"
| distinct DstGeoCountry, SrcGeoCountry

note: GeoCountry are optional in the schema
| parse kind=regex flags=U AdditionalExtensions with * "DstLocation=" DestinationCountry ';' * "SrcLocation=" SourceCountry : string
this is correct line.
1 best response

Accepted Solutions
best response confirmed by akshay250692 (Brass Contributor)
Solution
Generally if the Vendor doesn't supply the data you would have to try and look it up in another Table.

By the way you could have used ASIM for this (simple example):

_Im_NetworkSession_PaloAltoCEFV06(starttime=ago(1d))
// or use _ASim_NetworkSession if you dont need a parameter https://docs.microsoft.com/en-us/azure/sentinel/network-normalization-schema
| where Activity =~"Threat"
| distinct DstGeoCountry, SrcGeoCountry

note: GeoCountry are optional in the schema

View solution in original post