Skip to content

KQL Cafe - June 2024

Recording and Presentation

Hosts

Guests

News

You will find queries for the following use cases:

  • Monitoring Cosmos DB's request unit consumption.
  • Identifying top N queries by consumption in Cosmos DB.
  • Checking for requests that are throttled in Cosmos DB.
  • Checking for antivirus exclusions.
  • Identifying applications using auto proxy (WPAD).
  • Detecting changes to evade detection.
  • Analyzing Microsoft Graph API usage patterns.
  • Analyzing traffic patterns to Microsoft Graph APIs.
  • Monitoring ID Governance in Microsoft Entra for usage patterns.
  • Visualizing authentication method use over time.
  • Understanding administrative activities.
  • Monitoring short-lived connections in PostgreSQL.
  • Monitoring failed login attempts in PostgreSQL.
  • Resource utilization monitoring in PostgreSQL.
  • Summarizing sign-ins via iOS and macOS SSO Extensions.
  • Searching for MFA phone number changes using regex.
  • Tracking dynamic group membership changes.
  • Session breakdown by legacy vs modern TLS.
  • Understanding email authentication patterns for security.
  • Monitoring token protection impact and managing conditional access.
  • Analyzing Intune device management events and enrollments.
  • Monitoring failed operations and sign-in events.
  • Network traffic monitoring.
  • Identity governance operations.
  • Detecting suspicious activities and anomalies.
  • Identifying vulnerabilities and attack surfaces via IP range and CVE ID tracking.
  • Detecting administrative actions and user re-enabling.
  • High-risk sign-in patterns detection.
  • Reporting on antimalware versions.
  • Detecting email anomalies.
  • Performance troubleshooting for SQL servers.
  • Monitoring conditional access policy applications and failures

Our Guest Michalis Michalos

Our guest Michalis Michalos spoke about Defender for Endpoint and WSL.

Keeping an eye on WSL through Microsoft Defender for Endpoint

Identify endpoints that run WSL and/or MDE plug-in First things first, let’s identify endpoints that users run WSL with the following KQL:

DeviceProcessEvents
| where ActionType has "ProcessCreated"
| where ProcessVersionInfoOriginalFileName has "wsl.exe"
| where ProcessVersionInfoFileDescription has "Windows Subsystem for Linux"
| summarize by DeviceName

You can also identify which of your endpoints already have the MDE plug-in:

DeviceTvmSoftwareInventory
| where SoftwareName has "microsoft_defender_for_endpoint_plug-in_for_wsl"
| summarize by DeviceName

Joining forces from the queries above, you can identify endpoints running WSL but don’t have the plug-in installed:

let WSLDevices = DeviceProcessEvents
| where ActionType has "ProcessCreated"
| where ProcessVersionInfoOriginalFileName has "wsl.exe"
| where ProcessVersionInfoFileDescription has "Windows Subsystem for Linux"
| project DeviceName;
WSLDevices
    | join kind=leftanti (DeviceTvmSoftwareInventory
    | where SoftwareName has "microsoft_defender_for_endpoint_plug-in_for_wsl"
    | project DeviceName
) on DeviceName

A simple hunt to begin with could be the following, looking for reconnaissance activity:

let WSLSuspicousList = dynamic(["whoami", "uname", "find", "grep", "cron -l", "/etc/shadow", "/etc/passwd", "/etc/sudoers", "w"]); 
let TimeFrame = 30d; // Choose the best timeframe for your investigation
DeviceInfo
    | where RegistryDeviceTag has "WSL2"
    | project DeviceId
| join ( DeviceProcessEvents
    | where Timestamp > ago(TimeFrame)
    | where ActionType == "ProcessCreated"
    | where ProcessCommandLine has_any (WSLSuspicousList)
    | project TimeGenerated, WSLDeviceID = DeviceId, DeviceName, FileName, FolderPath, ProcessId, ProcessCommandLine, AccountDomain, AccountName
    )
on $left.DeviceId == $right.WSLDeviceID
| sort by TimeGenerated desc

Check out the above referenced blog post for more KQL for WSL.

Learn KQL

search "*castaldi*"

union withsource=Tables *

MicrosoftGraphActivityLogs
| where * contains "200"

MicrosoftGraphActivityLogs
| where ResponseStatusCode == "200"
| join kind=inner AADNonInteractiveUserSignInLogs on $left.SignInActivityId == $right.UniqueTokenIdentifier
| project-away *1

SigninLogs
| summarize arg_max(TimeGenerated,*) by ResultType
| project-keep TimeGenerated, OperationName, AppDisplayName, Risk*

What did you do with KQL this month

Monitor Azure Automation Account Runbooks

AzureDiagnostics
| where Category == 'JobLogs'
| extend RunbookName = RunbookName_s
| project TimeGenerated,RunbookName,ResultType,CorrelationId,JobId_g
| summarize StartTime = minif(TimeGenerated,ResultType == 'Started'),EndTime = minif(TimeGenerated,ResultType in ('Completed','Failed','Failed')),
Status = tostring(parse_json(make_list_if(ResultType,ResultType in ('Completed','Failed','Stopped')))[0]) by JobId_g,RunbookName
| extend DurationSec = datetime_diff('second', EndTime,StartTime)
| join kind=leftouter (AzureDiagnostics
| where Category == "JobStreams"
| where StreamType_s == "Error"
| summarize TotalErrors = dcount(StreamType_s) by JobId_g, StreamType_s)
on $left. JobId_g == $right. JobId_g
| extend HasErrors = iff(StreamType_s == 'Error',true,false)
| project StartTime, EndTime, DurationSec,RunbookName,Status,HasErrors,TotalErrors,JobId_g

Defender for Endpoint - internet-facing devices

JA3

show all SSL connections

DeviceNetworkEvents
| where ActionType == "SslConnectionInspected"

show all SSL connections and additional details (unpacked)

DeviceNetworkEvents
| where ActionType == "SslConnectionInspected"
| extend AF = parse_json(AdditionalFields)
| evaluate bag_unpack(AF)

show all SSL connections and join on successfull device network events to iidentify the executable JA3 (Client)

DeviceNetworkEvents
| where ActionType == "SslConnectionInspected"
| extend Array = parse_json(AdditionalFields)
| join kind=inner (
DeviceNetworkEvents 
| where ActionType == "ConnectionSuccess"
) on DeviceId, LocalIP, LocalPort, RemoteIP, RemotePort
| extend JA3 = tostring(Array.ja3), JA3S = tostring(Array.ja3s)
| summarize count() by Client = JA3, 
//Server = JA3S, 
InitiatingProcessFileName1, InitiatingProcessVersionInfoProductVersion1


show all SSL connections and count the successfull connections to a JA3S (Server)

```kql
DeviceNetworkEvents
| where ActionType == "SslConnectionInspected"
| extend Array = parse_json(AdditionalFields)
| join kind=inner (
DeviceNetworkEvents 
| where ActionType == "ConnectionSuccess"
) on DeviceId, LocalIP, LocalPort, RemoteIP, RemotePort
| extend JA3 = tostring(Array.ja3), JA3S = tostring(Array.ja3s)
| summarize count() by Server = JA3S, 
InitiatingProcessFileName1, InitiatingProcessVersionInfoProductVersion1, RemoteIP