Deployable detection rules

//Summarize which processes are triggering Lsass credential theft audit alerts in your attack surface reduction rules

//Data connector required for this query - M365 Defender - Device* tables

//Summarize each device by which processes are triggering the audit alert
DeviceEvents
| where TimeGenerated > ago (7d)
| where ActionType == "AsrLsassCredentialTheftAudited"
| extend isAudit = tostring(AdditionalFields.IsAudit)
| where isAudit = true
| summarize LsassAudit=make_set(InitiatingProcessCommandLine) by DeviceName
| extend ['Count of Processes']=array_length(LsassAudit)
| sort by ['Count of Processes'] desc 

//Change the query to summarize each process by which devices are triggering the audit alert
DeviceEvents
| where TimeGenerated > ago (7d)
| where ActionType == "AsrLsassCredentialTheftAudited"
| extend isAudit = tostring(AdditionalFields.IsAudit)
| where isAudit = true
| summarize LsassAudit=make_set(DeviceName) by InitiatingProcessCommandLine
| extend ['Count of Devices']=array_length(LsassAudit)
| sort by ['Count of Devices'] desc

//Detects when you have audit hits on the "Block all Office applications from creating child processes" ASR rule. 
//For instance if you want to audit the impact for the MSDT vulnerability - https://msrc-blog.microsoft.com/2022/05/30/guidance-for-cve-2022-30190-microsoft-support-diagnostic-tool-vulnerability/

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceEvents
| where TimeGenerated > ago (30d)
| where ActionType == "AsrOfficeChildProcessAudited"
| where AdditionalFields.IsAudit == true
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    FileName,
    ProcessCommandLine,
    InitiatingProcessFileName

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceEvents
| where Timestamp > ago (30d)
| where ActionType == "AsrOfficeChildProcessAudited"
| project
    Timestamp,
    DeviceName,
    InitiatingProcessAccountName,
    FileName,
    ProcessCommandLine,
    InitiatingProcessFileName

//Provides a summary of Attack Surface Reduction rules, which ASR rules are being hit and by which processes

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceEvents
| where TimeGenerated > ago(30d)
| where ActionType startswith "Asr"
| where isnotempty(InitiatingProcessCommandLine)
| summarize ['ASR Hit Count']=count()by ActionType, InitiatingProcessCommandLine
| sort by ['ASR Hit Count'] desc 

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceEvents
| where TimeGenerated > ago(30d)
| where ActionType startswith "Asr"
| where isnotempty(InitiatingProcessCommandLine)
| summarize ['ASR Hit Count']=count()by ActionType, InitiatingProcessCommandLine
| sort by ['ASR Hit Count'] desc

//Find which of your accounts have logged onto the most devices with local admin credentials. These accounts are potential targets for lateral movement and privilege escalation

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceLogonEvents
| where TimeGenerated > ago(30d)
| project DeviceName, ActionType, LogonType, AdditionalFields, InitiatingProcessCommandLine, AccountName, IsLocalAdmin
| where ActionType == "LogonSuccess"
| where LogonType == "Interactive"
| where AdditionalFields.IsLocalLogon == true
| where InitiatingProcessCommandLine == "lsass.exe"
| summarize
    ['Local Admin Count']=dcountif(DeviceName,IsLocalAdmin == "true"),
    ['Local Admins']=make_set_if(DeviceName, IsLocalAdmin == "true")
    by AccountName
| sort by ['Local Admin Count'] desc  

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceLogonEvents
| where Timestamp > ago(30d)
| project DeviceName, ActionType, LogonType, AdditionalFields, InitiatingProcessCommandLine, AccountName, IsLocalAdmin
| where ActionType == "LogonSuccess"
| where LogonType == "Interactive"
| where IsLocalAdmin == true
| where InitiatingProcessCommandLine == "lsass.exe"
| summarize
    ['Local Admin Count']=dcountif(DeviceName,IsLocalAdmin == "true"),
    ['Local Admins']=make_set_if(DeviceName, IsLocalAdmin == "true")
    by AccountName
| sort by ['Local Admin Count'] desc

// Searches device info table for non server operating systems then return any users who have logged on interactively as an admin as a set per device. Can add exclusions for known IT admin accounts

//Data connector required for this query - M365 Defender - Device* tables

let devices=
    DeviceInfo
    | where TimeGenerated > ago(30d)
    | where OSPlatform !contains "Server"
    | summarize arg_max(TimeGenerated, *) by DeviceName
    | project DeviceName;
DeviceLogonEvents
| where LogonType == "Interactive"
| where IsLocalAdmin == true
| join kind=inner devices on DeviceName
| where AccountName !contains "admin"
| summarize make_set(AccountName) by DeviceName

//Visualizes potentially anomalous RDP connections from your devices.

//Data connector required for this query - M365 Defender - Device* tables

//Starttime and timeframe = how many days of data to look at to build your data set and in what grouping, i.e 30 days of data over 2 hour periods.
//Threshold = the amount of total RDP connections required to be included in anomaly calculations. Reduces noise from low level anomalies, e.g going from 1 connection to 2.
//Sensitivity = adjust to make the query more or less sensitive, the higher the value, the greater the anomaly required to be detected.
let starttime = 30d;
let timeframe = 2h;
let sensitivity = 3;
let threshold = 5;
let outlierdevices=
    DeviceNetworkEvents
    | where TimeGenerated > ago(starttime)
    | where LocalIPType == "Private"
    | where RemotePort == "3389"
    // Exclude Defender for Identity which uses RDP to map your network
    | where InitiatingProcessFileName <> "Microsoft.Tri.Sensor.exe"
    | project TimeGenerated, DeviceName
    | order by TimeGenerated
    | summarize RDPEvents=count()by DeviceName, bin(TimeGenerated, timeframe)
    | where RDPEvents > threshold
    | summarize EventCount=make_list(RDPEvents), TimeGenerated=make_list(TimeGenerated) by DeviceName
    | extend outliers=series_decompose_anomalies(EventCount, sensitivity)
    | mv-expand TimeGenerated, EventCount, outliers
    | where outliers == 1
//Optionally visualize the anomalies, remove everything below this line to just retrieve the data
    | distinct DeviceName;
DeviceNetworkEvents
| where TimeGenerated > ago(starttime)
| where DeviceName in (outlierdevices)
| where LocalIPType == "Private"
| where RemotePort == "3389"
| where InitiatingProcessFileName <> "Microsoft.Tri.Sensor.exe"
| summarize RDPCount=count() by DeviceName, bin(TimeGenerated, timeframe)
| render timechart

//Detects when certutil is used to connect to a public IP. This could indicate abuse of cert util, see - https://www.avira.com/en/blog/certutil-abused-by-attackers-to-spread-threats

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceNetworkEvents
| where TimeGenerated > ago (7d)
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    LocalIPType,
    LocalIP,
    RemoteIPType,
    RemoteIP,
    RemoteUrl,
    RemotePort
| where InitiatingProcessCommandLine contains "certutil"
| where RemoteIPType == "Public"

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceNetworkEvents
| where TimeGenerated > ago (7d)
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    LocalIPType,
    LocalIP,
    RemoteIPType,
    RemoteIP,
    RemoteUrl,
    RemotePort
| where InitiatingProcessCommandLine contains "certutil"
| where RemoteIPType == "Public"

//Detect when a backup is taken from Windows Credential manager

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceEvents
| where ActionType == "CredentialsBackup"
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    InitiatingProcessFileName,
    InitiatingProcessFolderPath

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceEvents
| where ActionType == "CredentialsBackup"
| project
    Timestamp,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    InitiatingProcessFileName,
    InitiatingProcessFolderPath

//Finds encoded PowerShell commands and then decodes the encoded string

//Data connector required for this query - M365 Defender - Device* tables

//Query modified from this post - https://techcommunity.microsoft.com/t5/microsoft-sentinel/finding-base64-encoded-commands/m-p/1891876
DeviceProcessEvents
| where ProcessCommandLine contains "powershell" or InitiatingProcessCommandLine contains "powershell"
| where ProcessCommandLine contains "-enc"
    or ProcessCommandLine contains "-encodedcommand"
    or InitiatingProcessCommandLine contains "-enc"
    or InitiatingProcessCommandLine contains "-encodedcommand"
//Extract encoded command using regex
//This query will only return results when the command can be matched via regex and decoded, if you run only the above lines it will return all encoded commands without attempting to match and decode
| extend EncodedCommand = extract(@'\s+([A-Za-z0-9+/]{20}\S+$)', 1, ProcessCommandLine)
| where EncodedCommand != ""
| extend DecodedCommand = base64_decode_tostring(EncodedCommand)
| where DecodedCommand != ""
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    ProcessCommandLine,
    EncodedCommand,
    DecodedCommand

//Detects Teamviewer being used for the first time on a device

//Data connector required for this query - M365 Defender - Device* tables

//Build a list of known devices using Teamviewer over the last 60 days
let knowndevices=
DeviceNetworkEvents
| where TimeGenerated > ago(60d) and TimeGenerated < ago(1d)
| project DeviceName, InitiatingProcessFileName, ActionType, LocalIPType, RemoteIPType
| where InitiatingProcessFileName contains "teamviewer.exe"
| where ActionType == "ConnectionSuccess"
| where LocalIPType == "Private"
| where RemoteIPType == "Public"
| distinct DeviceName;
//Find new devices in the last day not in the known list
    DeviceNetworkEvents
    | where TimeGenerated > ago(1d)
    | where InitiatingProcessFileName contains "teamviewer.exe"
    | where ActionType == "ConnectionSuccess"
    | where LocalIPType == "Private"
    | where RemoteIPType == "Public"
    | where DeviceName !in (knowndevices)
    | distinct DeviceName

//Detects inbound RDP network connections from  public IP addresses

//Data connector required for this query - M365 Defender - Device* tables or Advanced Hunting license

//Works in both Microsoft Sentinel and Advanced Hunting
DeviceNetworkEvents
| where ActionType == "InboundConnectionAccepted"
| where LocalIPType == "Private" and RemoteIPType == "Public"
| where LocalPort == 3389 or InitiatingProcessCommandLine =~ "svchost.exe -k termsvcs -s TermService"
| project
    TimeGenerated,
    DeviceName,
    DeviceId,
    LocalIP,
    LocalPort,
    RemoteIP,
    InitiatingProcessCommandLine

//Detects successful TeamViewer connections from internal to external IP addresses

//Data connector required for this query - M365 Defender - Device* tables or Advanced Hunting license

//If TeamViewer is a sanctioned app then you will get lots of hits.
//Works in both Microsoft Sentinel and Advanced Hunting
DeviceNetworkEvents
| where InitiatingProcessFileName contains "teamviewer.exe"
| where ActionType == "ConnectionSuccess"
| where LocalIPType == "Private"
| where RemoteIPType == "Public"
| project TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessFileName,
    LocalIP,
    RemoteIP

//Detect invalid certificates

//Data connector required for this query - M365 Defender - Device* tables

DeviceEvents
| where ActionType == "AntivirusReport"
| extend ParsedAdditionalFields = parse_json(AdditionalFields)
| evaluate bag_unpack(ParsedAdditionalFields)
| where Signer startswith "INVALID"

// Searches for local admin log on events and then on process events that require full token elevation, query returns users who have logged on as an admin but not required admin access for 30 days

//Data connector required for this query - M365 Defender - Device* tables

// Exclude a list of known processes in your environment that require TokenElevationTypeFull such as conhost, DismHost
let process = dynamic(["conhost.exe", "DismHost.exe", "git.exe", "HPUpdate.exe"]);
let devices =
    DeviceInfo
    | where TimeGenerated > ago(30d)
    | where OSPlatform !contains "Server"
    | summarize arg_max(TimeGenerated, *) by DeviceName
    | project DeviceName
    | join kind=inner ( 
        DeviceLogonEvents
        | where TimeGenerated > ago (14d)
        | where LogonType == "Interactive"
// Exclude accounts such as service desk users who log on to complete admin work
        | where AccountName !contains "admin"
        | where IsLocalAdmin == true
        )
        on DeviceName
    | summarize arg_max (TimeGenerated, *) by DeviceName
    | project DeviceName, AccountName;
DeviceProcessEvents
| project
    TimeGenerated,
    DeviceName,
    AccountName,
    FileName,
    InitiatingProcessFileName,
    InitiatingProcessTokenElevation
| where TimeGenerated > ago(30d)
| where InitiatingProcessTokenElevation == "TokenElevationTypeFull"
| where FileName !in (process)
| join kind=rightanti devices on DeviceName, AccountName

//Detect when a local user account is created on an endpoint

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceEvents
| where TimeGenerated > ago(7d)
| where ActionType == "UserAccountCreated"
//Exclude defaultuser1 which is created by Windows through different processes 
| where AccountName != "defaultuser1"
| project
    TimeGenerated,
    DeviceName,
    ['Account Created Name']=AccountName,
    Actor=InitiatingProcessAccountName

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceEvents
| where Timestamp > ago(7d)
| where ActionType == "UserAccountCreated"
//Exclude defaultuser1 which is created by Windows through different processes 
| where AccountName != "defaultuser1"
| project
    Timestamp,
    DeviceName,
    ['Account Created Name']=AccountName,
    Actor=InitiatingProcessAccountName

//Detect allowed RDP connections from private to public networks

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceNetworkEvents
| where ActionType == "ConnectionSuccess"
| where RemotePort == "3389"
| where LocalIPType == "Private" and RemoteIPType == "Public"
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemoteUrl

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceNetworkEvents
| where ActionType == "ConnectionSuccess"
| where RemotePort == "3389"
| where LocalIPType == "Private" and RemoteIPType == "Public"
| project
    Timestamp,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemoteUrl

//When a Defender for Endpoint alert is triggered, search for the most recent interactive logon to the device prior to the alert.

//Data connector required for this query - M365 Defender - Device* tables
//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)

//Also returns if that user is a local admin on the device.
let timeframe = 48h;
SecurityAlert
| where TimeGenerated > ago(timeframe)
| where ProviderName == "MDATP"
| project AlertTime=TimeGenerated, DeviceName=CompromisedEntity, AlertName
| join kind=inner (
    DeviceLogonEvents
    | project
        TimeGenerated,
        LogonType,
        ActionType,
        InitiatingProcessCommandLine,
        IsLocalAdmin,
        AccountName,
        DeviceName
    | where LogonType in ("Interactive", "RemoteInteractive")
    | where ActionType == "LogonSuccess"
    | where InitiatingProcessCommandLine == "lsass.exe"
    )
    on DeviceName
| where (AlertTime - TimeGenerated) between (0min .. timeframe)
| summarize arg_max(TimeGenerated, *) by DeviceName
| project
    LogonTime=TimeGenerated,
    AlertTime,
    AlertName,
    DeviceName,
    AccountName,
    IsLocalAdmin

//Detect when an Excel macro connects to the internet. 
//Some IPs returned shown may be Microsoft telemetry but these events are still worth investigating.

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceNetworkEvents
| where InitiatingProcessFileName contains "excel.exe"
| where InitiatingProcessCommandLine contains ".xlsm" or InitiatingProcessCommandLine contains ".xltm"
//Exclude Microsoft telemetry endpoints 
| where RemoteUrl !endswith "outlook.com" 
    and RemoteUrl !endswith "office.com"
    and RemoteUrl !endswith "microsoft.com"
    and RemoteUrl !endswith "office365.com"
    and RemoteUrl !endswith "live.com"
    and RemoteUrl !endswith "office.net"
| where RemoteIPType == "Public"
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemotePort,
    RemoteUrl

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceNetworkEvents
| where InitiatingProcessFileName contains "excel.exe"
| where InitiatingProcessCommandLine contains ".xlsm" or InitiatingProcessCommandLine contains ".xltm"
| where RemoteIPType == "Public"
//Exclude Microsoft telemetry endpoints 
| where RemoteUrl !endswith "outlook.com" 
    and RemoteUrl !endswith "office.com"
    and RemoteUrl !endswith "microsoft.com"
    and RemoteUrl !endswith "office365.com"
    and RemoteUrl !endswith "live.com"
    and RemoteUrl !endswith "office.net"
| project
    Timestamp,
    DeviceName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemotePort,
    RemoteUrl

//Lookup Identity info table to find the most recent device a user has logged onto and any macro usage from that device and return identity info

//Data connector required for this query - M365 Defender - Device* tables
//Data connector required for this query - Microsoft Sentinel UEBA

let id=
    IdentityInfo
    | where TimeGenerated > ago (21d)
    | summarize arg_max(TimeGenerated, *) by AccountName
    | extend LoggedOnUser = AccountName
    | project LoggedOnUser, AccountUPN, JobTitle, EmployeeId, Country, City
    | join kind=inner (
        DeviceInfo
        | where TimeGenerated > ago (21d)
        | summarize arg_max(TimeGenerated, *) by DeviceName
        | extend LoggedOnUser = tostring(LoggedOnUsers[0].UserName)
        )
        on LoggedOnUser
    | project LoggedOnUser, AccountUPN, JobTitle, Country, DeviceName, EmployeeId;
DeviceProcessEvents
| join kind=inner id on DeviceName
| where TimeGenerated > ago (21d)
| where InitiatingProcessFileName == "EXCEL.EXE"
| where InitiatingProcessCommandLine contains ".xlsm" or InitiatingProcessCommandLine contains ".xltm"
| extend Process = InitiatingProcessFileName
| extend Command = InitiatingProcessCommandLine
| project
    TimeGenerated,
    DeviceName,
    LoggedOnUser,
    AccountUPN,
    Process,
    Command,
    JobTitle,
    EmployeeId,
    SHA1,
    SHA256

//Detect when the same IP attempts to brute force a remote connection or attempts to connect to multiple devices and fails over a short time period

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceLogonEvents
| where TimeGenerated > ago (1d)
| where LogonType == "RemoteInteractive"
| where ActionType == "LogonFailed"
| summarize
    ['Count of logon attempts']=count(),
    ['Count of distinct devices']=dcount(DeviceName),
    ['List of devices']=make_set(DeviceName)
    by RemoteIP, bin(TimeGenerated, 1h)
| where ['Count of distinct devices'] >= 3 or ['Count of logon attempts'] >= 10

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceLogonEvents
| where Timestamp > ago (1d)
| where LogonType == "RemoteInteractive"
| where ActionType == "LogonFailed"
| summarize
    ['Count of logon attempts']=count(),
    ['Count of distinct devices']=dcount(DeviceName),
    ['List of devices']=make_set(DeviceName)
    by RemoteIP, bin(Timestamp, 1h)
| where ['Count of distinct devices'] >= 3 or ['Count of logon attempts'] >= 10

//Detect when the same device attempts to connect to multiple devices and is denied within a short time frame
//This example will alert when the same device attempts to connect to three or more different devices in 30 minutes

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceEvents
| where ActionType == "FirewallInboundConnectionBlocked"
| summarize
    ['Count of Devices']=dcount(DeviceName),
    ['List of Devices']=make_set(DeviceName)
    by RemoteIP, bin(TimeGenerated, 30m)
| where ['Count of Devices'] >= 3

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceEvents
| where ActionType == "FirewallInboundConnectionBlocked"
| summarize
    ['Count of Devices']=dcount(DeviceName),
    ['List of Devices']=make_set(DeviceName)
    by RemoteIP, bin(Timestamp, 30m)
| where ['Count of Devices'] >= 3

//Detect successful internal to public connections using Putty

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceNetworkEvents
| where ActionType == "ConnectionSuccess"
| where LocalIPType == "Private"
| where RemoteIPType == "Public"
| where InitiatingProcessCommandLine contains "putty.exe"
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    LocalIP,
    RemoteIP,
    RemoteUrl,
    RemotePort

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceNetworkEvents
| where ActionType == "ConnectionSuccess"
| where LocalIPType == "Private"
| where RemoteIPType == "Public"
| where InitiatingProcessCommandLine contains "putty.exe"
| project
    Timestamp,
    DeviceName,
    InitiatingProcessAccountName,
    LocalIP,
    RemoteIP,
    RemoteUrl,
    RemotePort

//Search for devices connecting to multiple IP addresses via RDP witin a time window and alert when over a particular threshold

//Data connector required for this query - M365 Defender - Device* tables

let timerange=1d;
let window=20m;
let threshold=5;
DeviceNetworkEvents
| where TimeGenerated > ago(timerange)
| where ActionType == "ConnectionSuccess"
| where RemotePort == "3389"
// Exclude Defender for Identity which uses RDP to map your network
| where InitiatingProcessFileName <> "Microsoft.Tri.Sensor.exe"
| summarize ['Target Device List']=make_set(RemoteIP), ['Count of Devices']=dcount(RemoteIP) by bin(TimeGenerated, window), DeviceName
| where ['Count of Devices'] > threshold
| sort by ['Count of Devices'] desc

//Detect when a user or process attempts to tamper with Defender for Endpoint registry settings

//Data connector required for this query - M365 Defender - Device* tables

DeviceEvents
| where ActionType == "TamperingAttempt"
| extend OriginalRegistryValue = tostring(AdditionalFields.OriginalValue)
| extend Status = tostring(AdditionalFields.Status)
| extend TamperingAction = tostring(AdditionalFields.TamperingAction)
| extend AttemptedRegistryValue = tostring(AdditionalFields.TamperingAttemptedValue)
| extend TargetRegistryKey = tostring(AdditionalFields.Target)
| where TamperingAction == "RegistryModification"
| project
    TimeGenerated,
    DeviceName,
    TamperingAction,
    Status,
    OriginalRegistryValue,
    AttemptedRegistryValue,
    TargetRegistryKey,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine

//Detect when a user clears the security event log on one of your devices

//Data connector required for this query - M365 Defender - Device* tables

DeviceEvents
| where ActionType == "SecurityLogCleared"
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountDomain,
    InitiatingProcessAccountName

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceEvents
| where ActionType == "SecurityLogCleared"
| project
    Timestamp,
    DeviceName,
    InitiatingProcessAccountDomain,
    InitiatingProcessAccountName

//Detect when a user mounts an ISO file and then within 20 minutes launches a browser. These events could be unrelated because a BrowserLaunchedToOpenUrl event doesn't confirm if the lnk file was in the ISO file. 
//The detection is just based on time between mounting an ISO file and then launching a URL.

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
//Find events where an ISO file is mounted by detecting the creation of a .iso.lnk file
DeviceFileEvents
| where TimeGenerated > ago(1d)
| where ActionType == "FileCreated"
//This may create some false positives with files named iso.lnk at the end like summary-ciso.lnk
| where FileName endswith "iso.lnk"
| project
    ISOMountTime=TimeGenerated,
    DeviceName,
    FileName,
    FolderPath,
    InitiatingProcessAccountName
//Join to our DeviceEvents where a browser is opened to launch a URL
| join kind=inner(
    DeviceEvents
    | where TimeGenerated > ago(1d)
    | where ActionType == "BrowserLaunchedToOpenUrl"
    //Find only RemoteURLs that are web sites
    | where RemoteUrl startswith "http"
    | project
        URLOpenTime=TimeGenerated,
        DeviceName,
        InitiatingProcessAccountName,
        RemoteIP,
        RemoteUrl,
        RemotePort
    )
    on DeviceName, InitiatingProcessAccountName
//Find browser opened to URL events that happened within 20 minutes of the ISO file being mounted
| where URLOpenTime between ((ISOMountTime - timespan(0min)) .. (ISOMountTime + timespan(20min)))
| extend ['ISO FileName'] = trim(@".lnk", FileName)
| project
    ISOMountTime,
    URLOpenTime,
    ['ISO FileName'],
    DeviceName,
    InitiatingProcessAccountName,
    RemoteUrl,
    RemoteIP,
    RemotePort

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceFileEvents
| where Timestamp > ago(1d)
| where ActionType == "FileCreated"
//This may create some false positives with files named iso.lnk at the end like summary-ciso.lnk
| where FileName endswith "iso.lnk"
| project
    ISOMountTime=Timestamp,
    DeviceName,
    FileName,
    FolderPath,
    InitiatingProcessAccountName
//Join to our DeviceEvents where a browser is opened to launch a URL
| join kind=inner(
    DeviceEvents
    | where Timestamp > ago(1d)
    | where ActionType == "BrowserLaunchedToOpenUrl"
    //Find only RemoteURLs that are web sites
    | where RemoteUrl startswith "http"
    | project
        URLOpenTime=Timestamp,
        DeviceName,
        InitiatingProcessAccountName,
        RemoteIP,
        RemoteUrl,
        RemotePort
    )
    on DeviceName, InitiatingProcessAccountName
//Find browser opened to URL events that happened within 20 minutes of the ISO file being mounted
| where URLOpenTime between ((ISOMountTime - timespan(0min)) .. (ISOMountTime + timespan(20min)))
| extend ['ISO FileName'] = trim(@".lnk", FileName)
| project
    ISOMountTime,
    URLOpenTime,
    ['ISO FileName'],
    DeviceName,
    InitiatingProcessAccountName,
    RemoteUrl,
    RemoteIP,
    RemotePort

//Detects a user that downloaded a file from O365 and then wrote the same file to USB, matches on both filename and the user

//Data connector required for this query - M365 Defender - Device* tables
//Data connector required for this query - Office 365

let filedownloads=
    OfficeActivity
    | where TimeGenerated > ago(1d)
    | extend DownloadTime = TimeGenerated
    | where Operation in ('FileSyncDownloadedFull', 'FileDownloaded')
    | project DownloadTime, UserId, SourceFileName
    | join kind=inner
        (
        IdentityInfo
        | where TimeGenerated > ago (21d)
        | summarize arg_max(TimeGenerated, *) by AccountUPN) 
        on $left.UserId == $right.AccountUPN
    | project DownloadTime, SourceFileName, UserId, AccountName
;
DeviceEvents
| where TimeGenerated > ago(1d)
| where ActionType == "UsbDriveMounted"
| extend DriveLetter = tostring(todynamic(AdditionalFields).DriveLetter)
| join kind=inner 
    (
    DeviceFileEvents
    | where TimeGenerated > ago(1d)
    | extend FileCopyTime = TimeGenerated
    | where ActionType == "FileCreated"
    | join kind=inner filedownloads
        on
        $left.FileName == $right.SourceFileName,
        $left.RequestAccountName == $right.AccountName
    | parse FolderPath with DriveLetter '\\' *
    | extend DriveLetter = tostring(DriveLetter)
    ) 
    on DeviceId, DriveLetter
| extend FileCopied = FileName1
| extend User = AccountName1
| distinct DeviceName, DriveLetter, FileCopied, User

//Lookup members of a specific group and find any USB file copies completed by those users

//Data connector required for this query - M365 Defender - Device* tables
//Data connector required for this query - Microsoft Sentinel UEBA

let id=
    IdentityInfo
    | where GroupMembership has "Group Name"
    | where TimeGenerated > ago (21d)
    | summarize arg_max(TimeGenerated, *) by AccountName
    | extend LoggedOnUser = AccountName
    | project LoggedOnUser, AccountUPN, JobTitle, EmployeeId, Country, City
    | join kind=inner (
        DeviceInfo
        | where TimeGenerated > ago (21d)
        | summarize arg_max(TimeGenerated, *) by DeviceName
        | extend LoggedOnUser = tostring(LoggedOnUsers[0].UserName)
        )
        on LoggedOnUser
    | project LoggedOnUser, AccountUPN, JobTitle, Country, DeviceName, EmployeeId;
DeviceEvents
| where TimeGenerated > ago(30d)
| join kind=inner id on DeviceName
| where ActionType == "UsbDriveMounted"
| extend DriveLetter = tostring(todynamic(AdditionalFields).DriveLetter)
| join kind=inner (DeviceFileEvents
    | where TimeGenerated > ago(30d)
    | extend FileCopyTime = TimeGenerated
    | where ActionType == "FileCreated"
    | parse FolderPath with DriveLetter '\\' *
    | extend DriveLetter = tostring(DriveLetter)
    )
    on DeviceId, DriveLetter
| distinct
    TimeGenerated,
    DeviceName,
    DriveLetter,
    FileName1,
    LoggedOnUser,
    AccountUPN,
    JobTitle,
    EmployeeId,
    Country

// Devices without successful AV scan in the last n days
// As of 27.01.2022 only the following platforms are support
// Windows10, Windows10WVD, Windows11, WindowsServer2012R2, WindowsServer2016, WindowsServer2019, WindowsServer2022

//Data connector required for this query - Advanced Hunting license
//Query only works in Advanced Hunting

let Timerange = 14d;
DeviceInfo
| where OnboardingStatus == "Onboarded"
| where isnotempty( OSVersion)
| where Timestamp > ago(Timerange)
| summarize LastSeen = arg_max(Timestamp, *) by DeviceId
| extend LastSuccessfulAVScan = strcat("Not in the last ",format_timespan(Timerange,'d')," days")
| project LastSeen, DeviceId, DeviceName, MachineGroup, OSPlatform, OSVersion, DeviceType, LastSuccessfulAVScan, JoinType
// use rightsemi to return all devices that had a successful AV scan in the last n days
// use leftanti to return all devices that NOT had a successful AV scan in the last n days
| join kind=leftanti (
    DeviceEvents
    | where ActionType == "AntivirusScanCompleted"
    | where Timestamp > ago(Timerange)
    | summarize LastSuccessfulAVScan = max(Timestamp) by DeviceName, DeviceId
    | join kind=innerunique (
        DeviceInfo
        | where isnotempty( OSVersion )
    ) on DeviceId
    | summarize LastSeen = arg_max(Timestamp,*) by DeviceName
    | project LastSeen, DeviceId, DeviceName, MachineGroup, OSPlatform, OSVersion, DeviceType, LastSuccessfulAVScan, JoinType
) on DeviceId
| where OSPlatform in ("Windows10","Windows10WVD","Windows11","WindowsServer2012R2","WindowsServer2016","WindowsServer2019","WindowsServer2022")
| sort by DeviceType, MachineGroup, OSPlatform

//Summarize the machines in your environment that are triggering the most total ASR and distinct ASR rule events

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceEvents
| where TimeGenerated > ago (30d)
| where ActionType startswith "Asr"
| summarize
    ['Total ASR hit count']=count(),
    ['Distinct ASR rules count']=dcount(ActionType),
    ['List of ASR rules triggered']=make_set(ActionType),
    ['List of processess triggering ASR']=make_set(InitiatingProcessCommandLine)
    by DeviceName
| sort by ['Total ASR hit count'] desc 

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceEvents
| where Timestamp > ago (30d)
| where ActionType startswith "Asr"
| summarize
    ['Total ASR hit count']=count(),
    ['Distinct ASR rules count']=dcount(ActionType),
    ['List of ASR rules triggered']=make_set(ActionType),
    ['List of processess triggering ASR']=make_set(InitiatingProcessCommandLine)
    by DeviceName
| sort by ['Total ASR hit count'] desc

//Find devices that have stopped sending network events over the last 30 days, retrieve last event time and calculate the days since last event

//Data connector required for this query - M365 Defender - Device* tables

DeviceNetworkEvents
| project TimeGenerated, DeviceName
| where TimeGenerated > ago(365d)
| summarize arg_max(TimeGenerated, DeviceName) by DeviceName
| project DeviceName, ['Days Since Last Event'] = datetime_diff('day', now(), TimeGenerated), ['Last Event Time']=TimeGenerated
| where ['Days Since Last Event'] > 30
| sort by ['Days Since Last Event'] desc

//Find the information of any devices found by Defender network device discovery that are able to onboarded to Defender

//Data connector required for this query - Advanced Hunting license

//This query only works in Advanced Hunting
DeviceInfo
| summarize arg_max(Timestamp, *) by DeviceId 
| where OnboardingStatus == "Can be onboarded"
| where isempty(MergedToDeviceId)
| project ['Time last seen']=Timestamp, DeviceName, DeviceId, OSDistribution, OSVersion, DeviceCategory, IsAzureADJoined, JoinType

//Find the devices in your environment triggering the most Defender SmartScreen events

//Data connector required for this query - M365 Defender - Device* tables

DeviceEvents
| where TimeGenerated > ago (30d)
| where ActionType startswith "SmartScreen"
| extend Experience = tostring(AdditionalFields.Experience)
| where isnotempty(Experience)
| summarize
    ['Count of SmartScreen Events']=count(),
    ['List of SmartScreen Event Types']=make_set(Experience)
    by DeviceName
| sort by ['Count of SmartScreen Events'] desc 

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceEvents
| where Timestamp > ago(30d)
| where ActionType startswith "SmartScreen"
| extend AF = parse_json(AdditionalFields)
| extend Experience = tostring(AF.Experience)
| where isnotempty(Experience)
| summarize
    ['Count of SmartScreen Events']=count(),
    ['List of SmartScreen Event Types']=make_set(Experience)
    by DeviceName
| sort by ['Count of SmartScreen Events'] desc

//Find devices in your environment that have never triggered an ASR rule, you can likely turn on ASR for these devices without causing issues for the users.

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
//First find devices that have triggered an Attack Surface Reduction rule, either block or in audit mode.
let asrdevices=
    DeviceEvents
    | where TimeGenerated > ago (30d)
    | where ActionType startswith "Asr"
    | distinct DeviceName;
//Find all devices and exclude those that have previously triggered a rule
DeviceInfo
| where TimeGenerated > ago (30d)
| where OSPlatform startswith "Windows"
| summarize arg_max(TimeGenerated, *) by DeviceName
| where DeviceName !in (asrdevices)
| project
    ['Time Last Seen']=TimeGenerated,
    DeviceId,
    DeviceName,
    OSPlatform,
    OSVersion,
    LoggedOnUsers

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

//First find devices that have triggered an Attack Surface Reduction rule, either block or in audit mode.
let asrdevices=
    DeviceEvents
    | where Timestamp > ago (30d)
    | where ActionType startswith "Asr"
    | distinct DeviceName;
//Find all devices and exclude those that have previously triggered a rule
DeviceInfo
| where Timestamp > ago (30d)
| where OSPlatform startswith "Windows"
| summarize arg_max(Timestamp, *) by DeviceName
| where DeviceName  !in (asrdevices)
| project
    ['Time Last Seen']=Timestamp,
    DeviceId,
    DeviceName,
    OSPlatform,
    OSVersion,
    LoggedOnUsers

//Find potential network recon on one of your devices by detecting when it connects to more than 10 common ports used to map your network and 10 distinct endpoints within an hour
//Use that data to then return more details on each IP that device attempted to connect to

//Data connector required for this query - M365 Defender - Device* tables or Advanced Hunting license

//Query works in both Sentinel and Advanced Hunting
//First look for devices connecting to 10 or more common ports and 10 or more devices within an hour
let devices=
    DeviceNetworkEvents
    | where ActionType in ("ConnectionSuccess", "ConnectionFailed")
    | where RemotePort in ("21", "22", "25", "53", "80", "110", "123", "139", "389", "443", "445", "636", "993", "995", "8080")
    | where RemoteIPType == "Private"
    | summarize
        ['Total connection count']=count(),
        ['Count of distinct ports']=dcount(RemotePort),
        ['List of ports']=make_set(RemotePort),
        ['Count of remote IPs']=dcount(RemoteIP),
        ['List of remote IPs']=make_set(RemoteIP)
        by DeviceName, bin(TimeGenerated, 1h)
    | where ['Count of distinct ports'] >= 10 and ['Count of remote IPs'] >= 10
    | distinct DeviceName;
//Take those devices and return more detail on each attempt
DeviceNetworkEvents
| where ActionType in ("ConnectionSuccess", "ConnectionFailed")
| where RemotePort in ("21", "22", "25", "53", "80", "110", "123", "139", "389", "443", "445", "636", "993", "995", "8080")
| where DeviceName in (devices)
| where RemoteIPType == "Private"
| summarize
    ['Total connection count']=count(),
    ['Count of distinct ports']=dcount(RemotePort),
    ['List of ports']=make_set(RemotePort)
    by DeviceName, RemoteIP, ActionType
| project-reorder
    DeviceName,
    ActionType,
    RemoteIP,
    ['Total connection count'],
    ['Count of distinct ports'],
    ['List of ports']
| sort by DeviceName desc

//Find new devices onboarded to Defender in the last month

//Data connector required for this query - M365 Defender - Device* tables

let knowndevices=
    DeviceNetworkInfo
    | where TimeGenerated > ago (180d) and TimeGenerated < ago(30d)
    | distinct DeviceName;
DeviceNetworkInfo
| where TimeGenerated > ago (30d)
| summarize arg_min(TimeGenerated, *) by DeviceName
| where DeviceName !in (knowndevices)
| project TimeGenerated, DeviceName
| extend ['Days Since First Seen']=datetime_diff("day", now(), TimeGenerated)
| project ['Time First Seen']=TimeGenerated, ['Days Since First Seen'], DeviceName
| sort by ['Days Since First Seen'] desc

//Find any new DeviceEvents found in your environment over the last week compared to the last 6 months

//Data connector required for this query - M365 Defender - Device* tables

//First find existing action types over the six months
let knownevents=
    DeviceEvents
    | where TimeGenerated > ago (180d) and TimeGenerated < ago(7d)
    | distinct ActionType;
//Find new action types in the last week, the time they were first seen and how many counts seen this week
DeviceEvents
| where TimeGenerated > ago(7d)
| where ActionType !in (knownevents)
| summarize ['First Time Seen']=min(TimeGenerated), Count=count() by ActionType
| sort by Count desc

//Find which users clicked on a phishing link after it was detected by Defender for Office 365

//Data connector required for this query - M365 Defender - Device* tables
//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)

SecurityAlert
| where ProviderName == "OATP"
| where AlertName in ("Email messages containing malicious URL removed after delivery​","Email messages containing phish URLs removed after delivery")
| extend x = todynamic(Entities)
| mv-expand x
| parse-where x with * '"Url":"' MaliciousURL '"' *
| project PhishTime=TimeGenerated, MaliciousURL
| join kind=inner (
    DeviceEvents
    | project TimeGenerated, ActionType, DeviceName, InitiatingProcessAccountName, RemoteUrl
    | where ActionType == "BrowserLaunchedToOpenUrl"
    | where RemoteUrl startswith "http")
    on $left.MaliciousURL == $right.RemoteUrl
| project-rename URLOpenTime=TimeGenerated
| extend TimeDelta = abs(URLOpenTime - PhishTime)
| project PhishTime, URLOpenTime, TimeDelta, ActionType, RemoteUrl, DeviceName, InitiatingProcessAccountName

//Detect when a 'whoami' command is sent for the first time from a device & account combination not seen before

//Data connector required for this query - M365 Defender - Device* tables

DeviceProcessEvents
| where TimeGenerated > ago (30d) and TimeGenerated < ago(1d)
| project DeviceName, InitiatingProcessAccountName, InitiatingProcessCommandLine
| where InitiatingProcessCommandLine contains "whoami"
| distinct DeviceName, InitiatingProcessAccountName
| join kind=rightanti (
    DeviceProcessEvents
    | where TimeGenerated > ago(1d)
    | project
        TimeGenerated,
        DeviceName,
        InitiatingProcessAccountName,
        InitiatingProcessCommandLine
    | where InitiatingProcessCommandLine contains "whoami"
    )
    on DeviceName, InitiatingProcessAccountName

//Use the DeviceNetworkEvents to find what listening ports are being opened on a device and then query that list
//You can look for SSH, DNS etc being run from your end user devices

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceNetworkEvents
| where TimeGenerated > ago(7d)
| where ActionType == "ListeningConnectionCreated"
//Exclude high ports
| where LocalPort < 1025
| summarize ['Ports Opened']=make_set(LocalPort), ['Count of Ports Opened']=dcount(LocalPort) by DeviceName
//Look for machines running services such as FTP, SSH, DNS etc
| where ['Ports Opened'] has_any ("21","22","53")

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceNetworkEvents
| where Timestamp > ago(7d)
| where ActionType == "ListeningConnectionCreated"
//Exclude high ports
| where LocalPort < 1025
| summarize ['Ports Opened']=make_set(LocalPort), ['Count of Ports Opened']=dcount(LocalPort) by DeviceName
//Look for machines running services such as FTP, SSH, DNS etc
| where ['Ports Opened'] has_any ("21","22","53")

//Query the list of Known Exploited Vulnerabilities provided by CISA - https://www.cisa.gov/known-exploited-vulnerabilities-catalog and find any devices that have vulnerabilities known to be used with ransomware

//Data connector required for this query - Advanced Hunting license


let KEV=
externaldata(cveID: string, vendorProject: string, product: string, vulnerabilityName: string, dateAdded: datetime, shortDescription: string, requiredAction: string, dueDate: datetime, knownRansomwareCampaignUse:string,notes:string)
[
h@'https://www.cisa.gov/sites/default/files/csv/known_exploited_vulnerabilities.csv'
]
with(format='csv',ignorefirstrecord=true);
DeviceTvmSoftwareVulnerabilities
| project DeviceName, OSPlatform, cveID=CveId
| join kind=inner KEV on cveID
| where knownRansomwareCampaignUse =~ "Known"
| summarize ['Vulnerabilities']=make_set(cveID) by DeviceName
| extend ['Count of Known Exploited Vulnerabilities'] = array_length(['Vulnerabilities'])
| sort by ['Count of Known Exploited Vulnerabilities']


//This version looks specifically for internet facing devices

let KEV=
externaldata(cveID: string, vendorProject: string, product: string, vulnerabilityName: string, dateAdded: datetime, shortDescription: string, requiredAction: string, dueDate: datetime, knownRansomwareCampaignUse:string,notes:string)
[
h@'https://www.cisa.gov/sites/default/files/csv/known_exploited_vulnerabilities.csv'
]
with(format='csv',ignorefirstrecord=true);
let publicdevices=
DeviceInfo
| where IsInternetFacing
| summarize arg_max(Timestamp, *) by DeviceId
| distinct DeviceName;
DeviceTvmSoftwareVulnerabilities
| project DeviceName, OSPlatform, cveID=CveId
| join kind=inner KEV on cveID
| where knownRansomwareCampaignUse =~ "Known"
| where DeviceName in (publicdevices)
| summarize ['Vulnerabilities']=make_set(cveID), ['Count of Known Exploited Vulnerabilities']=dcount(cveID) by DeviceName

//Summarize all local user accounts (non-domain) per device which have logged on with administrative rights

//Data connector required for this query - M365 Defender - Device* tables

DeviceLogonEvents
| where TimeGenerated > ago(7d)
| project
    TimeGenerated,
    AdditionalFields,
    IsLocalAdmin,
    LogonType,
    DeviceName,
    AccountDomain,
    AccountName
| extend LocalLogon = toboolean(AdditionalFields.IsLocalLogon)
| where LocalLogon == true
| where IsLocalAdmin == true
| where LogonType == "Interactive"
| distinct AccountDomain, AccountName, DeviceName
//Split domain from device name to match for local logons
| extend Device = split(DeviceName, ".")[0]
| where Device == AccountDomain
| summarize ['Local Accounts with Admin']=make_set(AccountName), ['Count of Admin Accounts']=dcount(AccountName) by DeviceName
| sort by ['Count of Admin Accounts'] desc

//Retrieve any new ASR alerts in your environment over the last week not previously seen in the prior 90 days and which devices have triggered them

//Data connector required for this query - M365 Defender - Device* tables

let timerange=90d;
let existingalerts=
DeviceEvents
| where TimeGenerated > ago (timerange) and TimeGenerated < ago(7d)
| where ActionType startswith "Asr"
| distinct ActionType;
    DeviceEvents
    | where TimeGenerated > ago(7d)
    | where ActionType startswith "Asr"
    | where ActionType !in (existingalerts)
| summarize ['Device List']=make_set(DeviceName) by ActionType

//Detect when a process with a hash not previously seen before in your environment accesses lsass.exe via an open process API call

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
let knownhashes=
    DeviceEvents
    | where TimeGenerated > ago(30d) and TimeGenerated < ago (1d)
    | where ActionType == "OpenProcessApiCall"
    | where FileName == "lsass.exe"
    | distinct InitiatingProcessSHA256;
DeviceEvents
| where TimeGenerated > ago (1d)
| where ActionType == "OpenProcessApiCall"
| where FileName == "lsass.exe"
| where InitiatingProcessSHA256 !in (knownhashes)
| extend DesiredAccess = tostring(AdditionalFields.DesiredAccess)
| distinct
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    DesiredAccess

//Detect when a process with a hash not previously seen before in your environment accesses lsass.exe via an open process API call

//Data connector required for this query - Advanced Hunting license

//Advanced Hunting query
let knownhashes=
    DeviceEvents
    | where Timestamp > ago(30d) and Timestamp < ago (1d)
    | where ActionType == "OpenProcessApiCall"
    | where FileName == "lsass.exe"
    | distinct InitiatingProcessSHA256;
DeviceEvents
| where Timestamp > ago (1d)
| where ActionType == "OpenProcessApiCall"
| where FileName == "lsass.exe"
| where InitiatingProcessSHA256 !in (knownhashes)
| distinct DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    AdditionalFields

//Use the inbuilt KQL parse_url function to return the various components of a URL

//Data connector required for this query - M365 Defender - Device* tables

DeviceEvents
| where ActionType == "BrowserLaunchedToOpenUrl"
| extend Url = parse_url(RemoteUrl)
| extend Hostname = tostring(Url.Host)
| extend Fragment = tostring(Url.Fragment)
| extend Password = tostring(Url.Password)
| extend Path = tostring(Url.Path)
| extend Port = tostring(Url.Port)
| extend Query = tostring(Url.Query)
| extend Schema = tostring(Url.Scheme)
| extend Username = tostring(Url.Username)
| where isnotempty(Hostname)
| project
    TimeGenerated,
    DeviceName,
    RemoteUrl,
    Schema,
    Port,
    Hostname,
    Path,
    Query,
    Username,
    Password,
    Fragment

//Identifies potential DNS tunnelling over HTTPS 

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceNetworkEvents
| where ActionType == "NetworkSignatureInspected"
| extend AF = parse_json(AdditionalFields)
| extend NetworkSignature = AF.SignatureName
//Search for network signatures that are DNS but not on regular DNS ports including Netbios & LLMNR if those are in use
| where NetworkSignature == "DNS_Request" and RemotePort !in ("53", "137", "5353", "5355")
//Exclude traffic where the remote IP is a private/local IP address, you can remove this if also interested in that traffic
| where not(ipv4_is_private(RemoteIP))
| project
    TimeGenerated,
    DeviceName,
    NetworkSignature,
    LocalIP,
    LocalPort,
    RemoteIP,
    RemotePort,
    RemoteUrl

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceNetworkEvents
| where ActionType == "NetworkSignatureInspected"
| extend AF = parse_json(AdditionalFields)
| extend NetworkSignature = AF.SignatureName
//Search for network signatures that are DNS but not on regular DNS ports including Netbios & LLMNR if those are in use
| where NetworkSignature == "DNS_Request" and RemotePort !in ("53", "137", "5353", "5355")
//Exclude traffic where the remote IP is a private/local IP address, you can remove this if also interested in that traffic
| where not(ipv4_is_private(RemoteIP))
| project
    Timestamp,
    DeviceName,
    NetworkSignature,
    LocalIP,
    LocalPort,
    RemoteIP,
    RemotePort,
    RemoteUrl

//Detect when a non system account changes the PowerShell execution policy on a device

//Data connector required for this query - M365 Defender - Device* tables

DeviceEvents
| where ActionType == "PowerShellCommand"
| where InitiatingProcessFileName == "powershell.exe"
| where InitiatingProcessAccountName != "system"
| where AdditionalFields.Command == "Set-ExecutionPolicy"
| project TimeGenerated, DeviceName, InitiatingProcessAccountName

//Find users that are connecting to internet endpoints via PowerShell commands

//Data connector required for this query - M365 Defender - Device* tables

DeviceNetworkEvents
| project
    TimeGenerated,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    DeviceName,
    LocalIPType,
    LocalIP,
    RemoteIPType,
    RemotePort,
    RemoteIP,
    RemoteUrl
//Exclude system processes
| where InitiatingProcessAccountName != "system"
| where InitiatingProcessAccountName != "local service"
| where InitiatingProcessCommandLine contains "powershell"
| where LocalIPType == "Private"
| where RemoteIPType == "Public"
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemotePort,
    RemoteUrl
| sort by TimeGenerated desc

//Find when a process modifies the primary access token and parse the relevant details such as any privilege attached to the token, whether it is system level and the token integrity level

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceEvents
| where TimeGenerated > ago(30m)
| project
    DeviceName,
    ActionType,
    AdditionalFields,
    TimeGenerated,
    InitiatingProcessParentFileName,
    InitiatingProcessCommandLine
| where ActionType == "ProcessPrimaryTokenModified"
| extend TokenModificationProperties = AdditionalFields.TokenModificationProperties
| where isnotempty(TokenModificationProperties)
| parse TokenModificationProperties with * 'tokenChangeDescription":"' ['Token Change Description'] '","privilegesFlags":' ['Token Privileges'] ',"isChangedToSystemToken":' ['is Changed to System Token'] ',"originalTokenIntegrityLevelName":"' ['Original Token Level'] '","currentTokenIntegrityLevelName":"' ['Current Token Level'] '"' *
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessParentFileName,
    InitiatingProcessCommandLine,
    ['Original Token Level'],
    ['Current Token Level'],
    ['Token Privileges'],
    ['is Changed to System Token'],
    ['Token Change Description']

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceEvents
| where Timestamp > ago(30m)
| project
    DeviceName,
    ActionType,
    AdditionalFields,
    Timestamp,
    InitiatingProcessParentFileName,
    InitiatingProcessCommandLine
| where ActionType == "ProcessPrimaryTokenModified"
| extend AF = parse_json(AdditionalFields)
| extend OriginalTokenLevel = AF.OriginalTokenIntegrityLevel
| extend OriginalTokenPriv = AF.OriginalTokenPrivEnabled
| extend CurrentTokenLevel = AF.CurrentTokenIntegrityLevel
| extend CurrentTokenPriv = AF.CurrentTokenPrivEnabled
| extend TokenModification = AF.TokenModificationProperties

//Find successful connections from a private to public network on TCP port 22

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where ActionType == "ConnectionSuccess"
| where RemotePort == 22
| where LocalIPType == "Private"
| where RemoteIPType == "Public"
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemoteUrl

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceNetworkEvents
| where Timestamp > ago(1d)
| where ActionType == "ConnectionSuccess"
| where RemotePort == 22
| where LocalIPType == "Private"
| where RemoteIPType == "Public"
| project
    Timestamp,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemoteUrl