KQL Search

Query Details

Azure P2S Point To Site Connection Success Username And IP Parser

Query

//This will parse out username and local IP for Azure VPN connection success logs. Diagnostic settings must be enabled
let VPNlog = AzureDiagnostics
| where Message contains "Connection successful. Username=***"
| parse-kv Message as (Username:string,IP:string) with (pair_delimiter=' ', kv_delimiter='=')
| extend RedactedUserName = tolower(replace_string(Username,"***",""))
| project RedactedUserName,IP;
VPNlog //join on noninteractivelog where app id is Azure Public VPN, change app ID if required https://learn.microsoft.com/en-us/azure/vpn-gateway/openvpn-azure-ad-tenant?WT.mc_id=MVP_473477
| join (AADNonInteractiveUserSignInLogs | where AppId == "41b23e61-6c1e-4545-b367-cd054e0ed4b4" | extend newname = trim_start(@"\w{3}",UserPrincipalName)) on $left.RedactedUserName==$right.newname
| extend Country = parse_json(LocationDetails).countryOrRegion
| extend VT = iff(isnotempty(IPAddress),strcat(@"https://www.virustotal.com/gui/ip-address/",IPAddress),IPAddress)
//| where IPAddress != "" //You may want to filter our your own VPN IP as we are working with non-interactive logs
| project UserPrincipalName, IP, IPAddress, Country, VT, ResultType

Explanation

This KQL query is designed to analyze Azure VPN connection success logs by extracting specific information and correlating it with Azure Active Directory (AAD) non-interactive user sign-in logs. Here's a simplified breakdown of what the query does:

  1. Extract VPN Connection Details:

    • The query starts by filtering the AzureDiagnostics logs to find messages that indicate a successful VPN connection, specifically those containing "Connection successful. Username=***".
    • It then parses these messages to extract the Username and IP (local IP address) using key-value parsing.

  2. Redact and Normalize Username:

    • The extracted Username is processed to remove the placeholder "***" and convert it to lowercase, resulting in RedactedUserName.

  3. Join with AAD Non-Interactive Logs:

    • The query joins the VPN connection data with AADNonInteractiveUserSignInLogs based on the RedactedUserName and a trimmed version of the UserPrincipalName from the AAD logs.
    • It specifically looks for logs where the application ID corresponds to Azure Public VPN.

  4. Enhance with Additional Information:

    • The query extends the data with the Country information by parsing the LocationDetails field.
    • It also constructs a URL to VirusTotal for the IP address, allowing for further investigation if needed.

  5. Output Relevant Information:

    • Finally, the query projects (selects) the relevant fields: UserPrincipalName, IP (local IP), IPAddress, Country, VT (VirusTotal URL), and ResultType.

This query is useful for security and network administrators who need to monitor and analyze successful VPN connections, correlate them with user sign-ins, and potentially investigate any suspicious IP addresses.

Details

Jay Kerai profile picture

Jay Kerai

Released: October 3, 2025

Tables

AzureDiagnosticsAADNonInteractiveUserSignInLogs

Keywords

AzureDiagnosticsUsernameIPVPNConnectionLogsUserSignInLocationDetailsCountryRegionAddress

Operators

let|wherecontainsparse-kvaswithextendtolowerreplace_stringprojectjoinon==parse_jsoniffisnotemptystrcat!=

Actions

GitHub