KQL Search

Query Details

HomeAI ToolsDevice Query

Sentinel All In One UPN Threat Hunt

Query

// Sentinel All-In-One UPN ThreatHunt
// https://www.linkedin.com/pulse/microsoft-sentinel-kql-solo-leveling-steven-lim-8hsqc/

// This KQL query searches across these Sentinel log tables for the UPN variable that is defined at the start:
// Anomalies, CloudAppEvents, CommonSecurityLog, DeviceEvents, DeviceFileEvents, DeviceLogonEvents, AzureActivity, EmailEvents, OfficeActivity, SecurityEvent, BehaviorAnalytics

let upn = "[email protected]";
search in (Anomalies,CloudAppEvents,CommonSecurityLog,DeviceEvents,DeviceFileEvents,DeviceLogonEvents,AzureActivity,
EmailEvents,OfficeActivity,SecurityEvent,BehaviorAnalytics)
TimeGenerated between (ago(1d) .. now())
and (
// ** Events initiated by this UPN **
UserPrincipalName == upn                        // Anomalies, BehaviorAnalytics 
or tostring(RawEventData.UserId) == upn         // CloudAppEvents 
or SourceUserName == upn                        // CommonSecurityLog
or AccountName == upn                           // DeviceEvents
or InitiatingProcessAccountUpn  == upn          // DeviceFileEvents, DeviceLogonEvents  
or Caller == upn                                // AzureActivity
or RecipientEmailAddress == upn                 // EmailEvents
or SenderMailFromAddress == upn                 // EmailEvents
or UserId == upn                                // OfficeActivity
or Entities contains upn                        // SecurityEvent
)

Explanation

This KQL query is designed to search for activities related to a specific user, identified by their User Principal Name (UPN), across various log tables in Microsoft Sentinel. Here's a simple summary:

  1. Define the User: The query starts by defining the UPN of the user you're interested in, e.g., "[email protected]".

  2. Specify the Time Range: It searches for events that occurred within the last day (ago(1d) to now()).

  3. Search Across Multiple Tables: The query looks for this UPN in several log tables, including:

    • Anomalies
    • CloudAppEvents
    • CommonSecurityLog
    • DeviceEvents
    • DeviceFileEvents
    • DeviceLogonEvents
    • AzureActivity
    • EmailEvents
    • OfficeActivity
    • SecurityEvent
    • BehaviorAnalytics

  4. Match UPN in Different Fields: It checks various fields within these tables to see if they match the UPN. For example:

    • UserPrincipalName in Anomalies and BehaviorAnalytics
    • RawEventData.UserId in CloudAppEvents
    • SourceUserName in CommonSecurityLog
    • AccountName in DeviceEvents
    • InitiatingProcessAccountUpn in DeviceFileEvents and DeviceLogonEvents
    • Caller in AzureActivity
    • RecipientEmailAddress and SenderMailFromAddress in EmailEvents
    • UserId in OfficeActivity
    • Entities in SecurityEvent

In essence, this query helps you track all activities related to a specific user across multiple data sources within the last 24 hours.

Details

Steven Lim profile picture

Steven Lim

Released: August 2, 2024

Tables

AnomaliesCloudAppEventsCommonSecurityLogDeviceEventsDeviceFileEventsDeviceLogonEventsAzureActivityEmailEventsOfficeActivitySecurityEventBehaviorAnalytics

Keywords

SentinelAnomaliesCloudAppEventsCommonSecurityLogDeviceEventsDeviceFileEventsDeviceLogonEventsAzureActivityEmailEventsOfficeActivitySecurityEventBehaviorAnalytics

Operators

letsearchinbetweenagonowandortostringcontains

Actions