Skip to content

KQL Cafe - April 2025

Recording

Hosts

Guests

KQL News

Just another Kusto hacker (JAKH)

Your challenge is to write a Kusto query that outputs the string "Just another Kusto hacker". The query can be as simple or as complex as you like, as long as it is self-contained and can run on any Fabric EventHouse or Azure Data Explorer cluster.

Source

KustoCon 2025 Anouncement

KustoCon will run on November 6th, 2025

This year the KustoCon Conference will run in Hybrid Mode. With exclusive Workshops in the morning for those attending onsite and KustoCon sessions in the afternoon for both onsite and online participants.

We will share more details soon on KustoCon.com

The Ultimate Kusto Detective Challenge - Call of the Cyber Duty

There's a new Kusto Detective Agency challenge comming. This time you play in a team.

Your mission, should you choose to accept it: uncover the truth hidden in the data — and maybe, just maybe, save the world. Our headquarters are in Digitown — the world’s first fully observable city, where data isn’t just stored... it’s alive..........

Intro Video Register Cyber Duty Rules

OAuthAppInfo

The OAuthAppInfo table in the advanced hunting schema contains information about Microsoft 365-connected OAuth applications in the organization that are registered with Microsoft Entra ID and available in the Microsoft Defender for Cloud Apps app governance capability.

Source

Changes to the IdentityInfo table in Advanced Hunting

Microsoft Defender XDR services: Changes to the IdentityInfo table in Advanced Hunting IdentityInfo Microsoft Sentinel UEBA reference

Guest

Bert-Jan

This month we had Bert-Jan back on the show. Bert-Jan gave an inspiring talk on automation, using the various Microsoft Security APIs and creating reports.

References:

What did you do with KQL this month?

Defender for Endpoint - Identify Portable Apps

Auhtor: Alex

DeviceFileEvents
| where parse_json( AdditionalFields).FileType has_any ("PortableExecutable")
| extend FileExtension = parse_path(FolderPath).Extension
| where FileExtension == "exe"
| project FileName, FolderPath, FileOriginUrl, FileOriginReferrerUrl, AdditionalFields
| where isnotempty( FileOriginUrl)

DeviceFileEvents
| where parse_json( AdditionalFields).FileType has_any ("PortableExecutable")
| extend FileExtension = parse_path(FolderPath).Extension
| where FileExtension == "exe"
| project FileName, FolderPath, FileOriginUrl, FileOriginReferrerUrl, AdditionalFields
| where isnotempty( FileOriginUrl)
| summarize  Files = make_set(FileName), count() by FileOriginReferrerUrl

DeviceFileEvents
| where FileOriginReferrerUrl == "https://portableapps.com/"

DeviceProcessEvents
| where AccountName <> "system"
| where FolderPath matches regex @"^[A-Z]:\\.*$" // Any drive letter
    or FolderPath startswith @"\\" // Network shares
    or FolderPath matches regex @"^C:\\Users\\[^\\]+\\Downloads\\.*$" // Include C:\Users\*\Downloads
    or FolderPath matches regex @"^C:\\Users\\[^\\]+\\Desktop\\.*$" // Include C:\Users\*\Desktop
| where not(FolderPath matches regex @"^C:\\Windows\\.*$") // Exclude C:\Windows and subfolders
| where not(FolderPath matches regex @"^C:\\Program Files( \(x86\))?\\.*$") // Exclude C:\Program Files and Program Files (x86)
| where not(FolderPath matches regex @"^C:\\ProgramData\\.*$") // Exclude C:\ProgramData
| where not(AccountSid startswith "S-1-5-18") // Exclude Local System Account
| where not(AccountSid startswith "S-1-5-20") // Exclude Network Service Account
| project TimeGenerated, FileName, FolderPath, AccountName, AccountUpn, ProcessVersionInfoProductName

DeviceProcessEvents
| project TimeGenerated, FileName, FolderPath, AccountName, AccountUpn, ProcessVersionInfoInternalFileName, ProcessVersionInfoOriginalFileName, ProcessVersionInfoProductName
| where ProcessVersionInfoProductName has "portable"

Source

Defender for Office 365 - Identify Non-RFC Compliant Emails

Author: Alex

EmailEvents
| where Timestamp >= ago(90d)
| where not(SenderFromAddress matches regex @"^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)*$")
| project Timestamp,
          SenderMailFromAddress,
          SenderFromAddress,
          Subject,
          RecipientEmailAddress,
          DeliveryAction,
          NetworkMessageId
| order by Timestamp desc
| summarize count() by SenderFromAddress

Source