KQL Search

Query Details

HomeAI ToolsDevice Query

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
| 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 (Kusto Query Language) query is designed to analyze Azure VPN connection success logs and correlate them 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:

    • It starts by filtering the AzureDiagnostics table to find logs where the message indicates a successful VPN connection and contains a username.
    • It parses the message to extract the Username and IP (local IP address) from the log.

  2. Redact and Normalize Username:

    • The username is processed to remove a placeholder ("***") and convert it to lowercase, resulting in RedactedUserName.

  3. Project Relevant Fields:

    • It selects (projects) only the redacted username and IP for further processing.

  4. Join with AAD Non-Interactive Logs:

    • The query joins the VPN logs with AADNonInteractiveUserSignInLogs based on the username. It specifically looks for logs where the application ID corresponds to Azure Public VPN.
    • The UserPrincipalName from AAD logs is trimmed to match the redacted username format.

  5. Extract Additional Information:

    • It extracts the country or region from the LocationDetails JSON field.
    • It constructs a URL to VirusTotal for the IP address if it exists, allowing for further investigation of the IP.

  6. Project Final Output:

    • The final output includes the UserPrincipalName, local IP (IP), public IP (IPAddress), country, VirusTotal URL (VT), and the result type.

This query is useful for security and compliance purposes, as it helps track successful VPN connections and correlate them with user sign-ins, providing insights into the geographic location and potential security risks associated with the IP addresses used.

Details

Jay Kerai profile picture

Jay Kerai

Released: January 9, 2025

Tables

AzureDiagnosticsAADNonInteractiveUserSignInLogs

Keywords

AzureDiagnosticsUserIPAddressCountryLocation

Operators

letwhereparse-kvaswithextendtolowerreplace_stringprojectjoinontrim_startparse_jsoniffisnotemptystrcat

Actions