Query Details
Ipv4 Is Private Malfunction
Query
DnsEvents
| extend QueriedIPAddress = extract(strcat(@"((?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?))$"), 1, Name)
| where not(isempty(QueriedIPAddress) or QueriedIPAddress == "127.0.0.1")
| distinct Name, QueriedIPAddress
| project
Name,
QueriedIPAddress,
Col1 = parse_ipv4(QueriedIPAddress),
Col2 = isnotempty(parse_ipv4(QueriedIPAddress)),
Col3 = ipv4_is_private(QueriedIPAddress),
Col4 = isnotempty(parse_ipv4(QueriedIPAddress)) and ipv4_is_private(QueriedIPAddress),
Col5 = not(isnotempty(parse_ipv4(QueriedIPAddress)) and ipv4_is_private(QueriedIPAddress))
| where not(isnotempty(parse_ipv4(QueriedIPAddress)) and ipv4_is_private(QueriedIPAddress))
// | where Col5
Explanation
This query is analyzing DNS events and extracting the queried IP address from the "Name" field. It then filters out any empty or local IP addresses (127.0.0.1). The query then returns distinct combinations of the "Name" and "QueriedIPAddress" fields. It also creates additional columns to check if the IP address is valid, if it is not empty, if it is a private IP address, and if it is both not empty and not a private IP address. Finally, it filters out any rows where the IP address is not empty and not a private IP address.
Details
Jose Sebastián Canós
Released: January 10, 2024
Tables
DnsEvents
Keywords
Devices,Intune,User
Operators
extendextractwherenotisempty==distinctprojectparse_ipv4isnotemptyipv4_is_privateandor