Deployable detection rules

//Detect SSH traffic that isn't on port 22 connecting to public IP addresses

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

//Microosft Sentinel query
DeviceNetworkEvents
| where ActionType == "NetworkSignatureInspected"
| extend AF = parse_json(AdditionalFields)
| extend NetworkSignature = AF.SignatureName
//Search for network signatures that are SSH but not on port 22
| where NetworkSignature == "SSH" and RemotePort != 22
//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))
//Exclude traffic where the remote IP is a Link Local address
| where not(ipv4_is_match(RemoteIP,'169.0.0.0/8')) | 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 SSH but not on port 22
| where NetworkSignature == "SSH" and RemotePort != 22
//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))
//Exclude traffic where the remote IP is a Link Local address
| where not(ipv4_is_match(RemoteIP,'169.0.0.0/8')) 
| project
    Timestamp,
    DeviceName,
    NetworkSignature,
    LocalIP,
    LocalPort,
    RemoteIP,
    RemotePort,
    RemoteUrl

//Create a summary of the devices with inbound LDAP and LDAPS connections, sorted by the devices with the most inbound LDAP

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

//Query works in both Microsoft Sentinel and Advanced Hunting
DeviceNetworkEvents
| where ActionType == "InboundConnectionAccepted"
| where LocalPort in ("389", "636", "3269")
| summarize
    ['Count of Inbound LDAP Connections']=countif(LocalPort == 389),
    ['Count of Distinct Inbound LDAP Connections']=dcountif(RemoteIP, LocalPort == 389),
    ['List of Inbound LDAP Connections']=make_set_if(RemoteIP, LocalPort == 389),
    ['Count of Inbound LDAPS Connections']=countif(LocalPort in ("636", "3269")),
    ['Count of Distinct Inbound LDAPS Connections']=dcountif(RemoteIP, LocalPort in ("636", "3269")),
    ['List of Inbound LDAPS Connections']=make_set_if(RemoteIP, LocalPort in ("636", "3269"))
    by DeviceName
| sort by ['Count of Distinct Inbound LDAP Connections'] desc

//Summarize the total count of all local group additions by group name

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

DeviceEvents
| where TimeGenerated > ago (30d)
| where ActionType == "UserAccountAddedToLocalGroup"
| summarize ['Local Group Addition Count']=count() by ['Local Group Name']=tostring(AdditionalFields.GroupName)
| sort by ['Local Group Addition Count']

//Summarize the local (non domain) logon activity for your devices for both successful and failed logons. You may have users using a local account to bypass security policy

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

//Microsoft Sentinel query
DeviceLogonEvents
| where TimeGenerated > ago(30d)
//Find logons where AccountDomain == DeviceName indicating a local logon
| where AccountDomain == DeviceName
| where AdditionalFields.IsLocalLogon == true
| where LogonType == "Interactive"
| where RemoteIPType != "Loopback"
| summarize
    ['Count of successful local logon attempts']=countif(ActionType == "LogonSuccess"),
    ['Distinct count of successful local logon attempts']=dcountif(AccountName, ActionType == "LogonSuccess"),
    ['List of succesful local account logons']=make_set_if(AccountName, ActionType == "LogonSuccess"),
    ['Count of failed local logon attempts']=countif(ActionType == "LogonFailed"),
    ['Distinct count of failed local logon attempts']=dcountif(AccountName, ActionType == "LogonFailed"),
    ['List of failed local account logons']=make_set_if(AccountName, ActionType == "LogonFailed")
    by DeviceName
| project-reorder
    DeviceName,
    ['Count of successful local logon attempts'],
    ['Distinct count of successful local logon attempts'],
    ['List of succesful local account logons'],
    ['Count of failed local logon attempts'],
    ['Distinct count of failed local logon attempts'],
    ['List of failed local account logons']

//Advanced Hunting query

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

DeviceLogonEvents
| where Timestamp > ago(30d)
| where AccountDomain == DeviceName
| where LogonType == @"Interactive"
| where RemoteIPType != "Loopback"
| summarize
    ['Count of successful local logon attempts']=countif(ActionType == "LogonSuccess"),
    ['Distinct count of successful local logon attempts']=dcountif(AccountName, ActionType == "LogonSuccess"),
    ['List of succesful local account logons']=make_set_if(AccountName, ActionType == "LogonSuccess"),
    ['Count of failed local logon attempts']=countif(ActionType == "LogonFailed"),
    ['Distinct count of failed local logon attempts']=dcountif(AccountName, ActionType == "LogonFailed"),
    ['List of failed local account logons']=make_set_if(AccountName, ActionType == "LogonFailed")
    by DeviceName
| project-reorder
    DeviceName,
    ['Count of successful local logon attempts'],
    ['Distinct count of successful local logon attempts'],
    ['List of succesful local account logons'],
    ['Count of failed local logon attempts'],
    ['Distinct count of failed local logon attempts'],
    ['List of failed local account logons']

//Summarize macro usage on your devies by creating a list all macros used, a count of how many users are using each one and the account names

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

//Macro usage may be double counted if the same file is executed from two locations, i.e from a network share and a local drive.
//Microsoft Sentinel query
union DeviceFileEvents, DeviceNetworkEvents
| where TimeGenerated > ago(30d)
| project InitiatingProcessCommandLine, InitiatingProcessAccountName
| where InitiatingProcessCommandLine startswith '"EXCEL.EXE'  
| where InitiatingProcessCommandLine endswith '.xltm"' or InitiatingProcessCommandLine endswith '.xlsm"'
//Retrieve distinct values for process, hash and account
| distinct InitiatingProcessCommandLine, InitiatingProcessAccountName
//Parse the file path and file name from the process
| parse-where InitiatingProcessCommandLine with * '"EXCEL.EXE" "' ['Macro Filename'] '"' *
//Summarize the list of macro files by which users have used them
| summarize ['List of Users']=make_set(InitiatingProcessAccountName), ['Count of Users']=dcount(InitiatingProcessAccountName) by ['Macro Filename']
| sort by ['Count of Users'] desc 

//Advanced Hunting query

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

union DeviceFileEvents, DeviceNetworkEvents
| where Timestamp > ago(30d)
| project InitiatingProcessCommandLine, InitiatingProcessAccountName
| where InitiatingProcessCommandLine startswith '"EXCEL.EXE'  
| where InitiatingProcessCommandLine endswith '.xltm"' or InitiatingProcessCommandLine endswith '.xlsm"'
//Retrieve distinct values for process, hash and account
| distinct InitiatingProcessCommandLine, InitiatingProcessAccountName
//Parse the file path and file name from the process
| parse-where InitiatingProcessCommandLine with * '"EXCEL.EXE" "' ['Macro Filename'] '"' *
//Summarize the list of macro files by which users have used them
| summarize ['List of Users']=make_set(InitiatingProcessAccountName), ['Count of Users']=dcount(InitiatingProcessAccountName) by ['Macro Filename']
| sort by ['Count of Users'] desc

//Summarize your devices by their RDP activity. The data is sorted to show total outbound RDP connections, a count of distinct RDP connections and the list of IP's connected to.

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

//Data is sorted by the devices with the most unique outbound RDP connections. Those devices have the biggest lateral movement blast radius.
//Microsoft Sentinel query
DeviceNetworkEvents
| where TimeGenerated > ago(30d)
| where ActionType == "ConnectionSuccess"
| where RemotePort == "3389"
//Exclude Defender for Identity that uses an initial RDP connection to map your network
| where InitiatingProcessCommandLine <> "\"Microsoft.Tri.Sensor.exe\""
| summarize
    ['RDP Outbound Connection Count']=count(),
    ['RDP Distinct Outbound Endpoint Count']=dcount(RemoteIP),
    ['RDP Outbound Endpoints']=make_set(RemoteIP)
    by DeviceName
| sort by ['RDP Distinct Outbound Endpoint Count'] desc 

//Advanced Hunting query

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

DeviceNetworkEvents
| where Timestamp > ago(30d)
| where ActionType == "ConnectionSuccess"
| where RemotePort == "3389"
//Exclude Defender for Identity that uses an initial RDP connection to map your network
| where InitiatingProcessCommandLine <> "\"Microsoft.Tri.Sensor.exe\""
| summarize
    ['RDP Outbound Connection Count']=count(),
    ['RDP Distinct Outbound Endpoint Count']=dcount(RemoteIP),
    ['RDP Outbound Endpoints']=make_set(RemoteIP)
    by DeviceName
| sort by ['RDP Distinct Outbound Endpoint Count'] desc

//Find any devices enrolled into Defender that have created an inbound listening connection on port 22 and retrieve the process command line that opened the connection

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

//Microsoft Sentinel query
DeviceNetworkEvents
| where TimeGenerated > ago(7d)
| where ActionType == "ListeningConnectionCreated"
| where LocalPort == 22
| summarize
    ['Total count of listening connections opened']=count(),
    ['List of processes creating listening connections']=make_set(InitiatingProcessCommandLine)
    by DeviceName
| sort by ['Total count of listening connections opened'] desc 

//Advanced Hunting query

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

DeviceNetworkEvents
| where Timestamp > ago(7d)
| where ActionType == "ListeningConnectionCreated"
| where LocalPort == 22
| summarize
    ['Total count of listening connections opened']=count(),
    ['List of processes creating listening connections']=make_set(InitiatingProcessCommandLine)
    by DeviceName
| sort by ['Total count of listening connections opened'] desc

//Summarize the domains that Smartscreen is blocking as phishing attempts

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

DeviceEvents
| where TimeGenerated > ago (30d)
| where ActionType startswith "SmartScreen"
| extend SmartScreenExperience = tostring(AdditionalFields.Experience)
| where AdditionalFields.Experience == "Phishing"
| parse-where RemoteUrl with * '://' RemoteDomain '/' *
| summarize Count=count()by RemoteDomain
| sort by Count

//Summarize the most common files in your environment flagging Smartscreen untrusted warnings

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

//Microsoft Sentinel query
DeviceEvents
| where TimeGenerated > ago (30d)
| where ActionType startswith "SmartScreen"
| extend SmartScreenExperience = tostring(AdditionalFields.Experience)
| where SmartScreenExperience == "Untrusted"
| summarize Count=count()by FileName
| sort by Count

//Advanced Hunting query

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

DeviceEvents
| where Timestamp > ago(30d)
| where ActionType startswith "SmartScreen"
| where AdditionalFields == @"{""Experience"":""Untrusted""}"
| summarize Count=count()by FileName
| sort by Count

//Create a summary of each device showing all users who have logged on, separated into normal and local admin logons

//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(AccountName,IsLocalAdmin == "true"),
    ['Local Admins']=make_set_if(AccountName, IsLocalAdmin == "true"), 
    ['Standard User Count']=dcountif(AccountName, IsLocalAdmin == "false"),
    ['Standard Users']=make_set_if(AccountName, IsLocalAdmin == "false")
    by DeviceName
| 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 InitiatingProcessCommandLine == "lsass.exe"
| summarize
    ['Local Admin Count']=dcountif(AccountName,IsLocalAdmin == "true"),
    ['Local Admins']=make_set_if(AccountName, IsLocalAdmin == "true"), 
    ['Standard User Count']=dcountif(AccountName, IsLocalAdmin == "false"),
    ['Standard Users']=make_set_if(AccountName, IsLocalAdmin == "false")
    by DeviceName
| sort by ['Local Admin Count'] desc

//Top 20 departments copying file data to USB drives by file count

//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, Department, AccountDisplayName
| 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, Department, AccountDisplayName;
DeviceEvents
| where TimeGenerated > ago(7d)
| join kind=inner id on DeviceName
| where ActionType == "UsbDriveMounted"
| extend Type = tostring(AdditionalFields.Manufacturer)
| extend DriveLetter = tostring(todynamic(AdditionalFields).DriveLetter)
| join kind=inner (DeviceFileEvents
| where TimeGenerated > ago(7d)
| extend FileCopyTime = TimeGenerated
| where ActionType == "FileCreated"
| parse FolderPath with DriveLetter '\\' *
| extend DriveLetter = tostring(DriveLetter)
) on DeviceId, DriveLetter
| extend FileSizeGB = FileSize/1024/1024/1000
| project TimeGenerated, DeviceName, DriveLetter, FileName1, FileSizeGB, LoggedOnUser, AccountUPN, JobTitle,EmployeeId, Country, Department, Type, AccountDisplayName
| summarize CopyCount=count()by Department
| order by CopyCount
| take 20
| render columnchart

//Top 20 departments copying file data to USB drives by file size

//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, Department, AccountDisplayName
| 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, Department, AccountDisplayName;
DeviceEvents
| where TimeGenerated > ago(7d)
| join kind=inner id on DeviceName
| where ActionType == "UsbDriveMounted"
| extend Type = tostring(AdditionalFields.Manufacturer)
| extend DriveLetter = tostring(todynamic(AdditionalFields).DriveLetter)
| join kind=inner (DeviceFileEvents
| where TimeGenerated > ago(7d)
| extend FileCopyTime = TimeGenerated
| where ActionType == "FileCreated"
| parse FolderPath with DriveLetter '\\' *
| extend DriveLetter = tostring(DriveLetter)
) on DeviceId, DriveLetter
| extend FileSizeGB = FileSize/1024/1024/1000
| project TimeGenerated, DeviceName, DriveLetter, FileName1, FileSizeGB, LoggedOnUser, AccountUPN, JobTitle,EmployeeId, Country, Department, Type, AccountDisplayName
| summarize CopySize=sum(FileSizeGB)by Department
| order by CopySize
| take 20
| render columnchart

//Find the top 20 of a collection of varied data sets, no real detections in here just interesting data that is captured

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

//Microsft Sentinel query

//Top 20 USB models plugged in
DeviceEvents
| where ActionType == "UsbDriveMounted"
| extend Manufacturer = tostring(AdditionalFields.Manufacturer)
| extend ProductName = tostring(AdditionalFields.ProductName)
| where isnotempty(Manufacturer) or isnotempty(Manufacturer)
| extend ['USB Drive Model']= strcat(Manufacturer, "-", ProductName)
| summarize Count=count()by ['USB Drive Model']
| top 20 by Count

//Top 20 users taking screenshots
DeviceEvents
| where ActionType == "ScreenshotTaken"
| where InitiatingProcessAccountName != "system"
| summarize Count=count() by InitiatingProcessAccountName
| top 20 by Count

//Top 20 models of monitor being plugged in
DeviceEvents
| where ActionType == "PnpDeviceConnected"
| extend ClassName = tostring(AdditionalFields.ClassName)
| where ClassName == "Monitor"
| extend ['Monitor Type'] = tostring(AdditionalFields.DeviceDescription)
| summarize Count=count()by ['Monitor Type']
| top 20 by Count

//Top 20 web shortcuts opened
DeviceEvents
| where ActionType == "BrowserLaunchedToOpenUrl"
| summarize Count=count()by RemoteUrl
| where RemoteUrl startswith "http"
| top 20 by Count


//Advanced Hunting queries

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

//Top 20 USB models plugged in
DeviceEvents
| where ActionType == "UsbDriveMounted"
| extend AF = parse_json(AdditionalFields)
| extend Manufacturer = tostring(AF.Manufacturer)
| extend ProductName = tostring(AF.ProductName)
| where isnotempty(Manufacturer) or isnotempty(Manufacturer)
| extend ['USB Drive Model']= strcat(Manufacturer, "-", ProductName)
| summarize Count=count()by ['USB Drive Model']
| top 20 by Count

//Top 20 users taking screenshots
DeviceEvents
| where ActionType == "ScreenshotTaken"
| where InitiatingProcessAccountName != "system"
| summarize Count=count() by InitiatingProcessAccountName
| top 20 by Count

//Top 20 models of monitor being plugged in
DeviceEvents
| where ActionType == "PnpDeviceConnected"
| extend AF = parse_json(AdditionalFields)
| extend ClassName = tostring(AF.ClassName)
| where ClassName == "Monitor"
| extend ['Monitor Type'] = tostring(AF.DeviceDescription)
| summarize Count=count()by ['Monitor Type']
| top 20 by Count

//Top 20 web shortcuts opened
DeviceEvents
| where ActionType == "BrowserLaunchedToOpenUrl"
| summarize Count=count()by RemoteUrl
| where RemoteUrl startswith "http"
| top 20 by Count

//Detect when an admin adds another user to the local administrators group on a device and optionally query IdentityInfo to return the UPN of the user added

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

DeviceEvents
| where TimeGenerated > ago(7d)
| where ActionType == "UserAccountAddedToLocalGroup"
| where AdditionalFields.GroupName == "Administrators"
// Exclude processes initiated by system as this detection is for end users adding groups
| where InitiatingProcessAccountSid != "S-1-5-18"
| project
    TimeGenerated,
    DeviceName,
    AccountSid,
    Actor=InitiatingProcessAccountName
//Join query to IdentityInfo table to match the AccountSid
//if you do not use the IdentityInfo table remove everything below this line
| join kind=inner (
    IdentityInfo
    | where TimeGenerated > ago (21d)
    | summarize arg_max (TimeGenerated, *) by AccountUPN)
    on $left.AccountSid == $right.AccountSID
| project
    TimeGenerated,
    DeviceName,
    ['User Added']=AccountUPN,
    ['User Added Sid']=AccountSID,
    Actor


//Advanced Hunting query

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

DeviceEvents
| where Timestamp > ago(30d)
| where ActionType == "UserAccountAddedToLocalGroup"
| where AdditionalFields contains "Administrator"
| where InitiatingProcessAccountSid != "S-1-5-18"
| project DeviceName, Actor=InitiatingProcessAccountName, AccountSid
| join kind=inner (
IdentityInfo
)
on $left.AccountSid==$right.OnPremSid
| project DeviceName, Actor, AccountSid, UserAdded=AccountUpn

//Visualize your total attack surface reduction rule events over time with trend

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

let StartDate = now(-90d);
let EndDate = now();
DeviceEvents
| where ActionType startswith "Asr"
| make-series ['Total ASR Events']=count() on TimeGenerated in range(StartDate, EndDate, 1d)
| extend (RSquare, SplitIdx, Variance, RVariance, TrendLine)=series_fit_2lines(['Total ASR Events'])
| project TimeGenerated, ['Total ASR Events'], Trend=TrendLine
| render timechart
    with (
    xtitle="Day",
    ytitle="Count of ASR Events",
    title="Attack surface reduction events over time with trend")

//Visualize the most common domains triggering Microsoft Defender SmartScreen warnings

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

DeviceEvents
| where TimeGenerated > ago (30d)
| where ActionType == "SmartScreenUrlWarning"
| parse RemoteUrl with * '://' Domain '/' *
| where isnotempty(Domain)
| summarize Count=count()by Domain
| sort by Count
| render barchart

//Visualize the most common ISO files being mounted on your devices

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

//Microsoft Sentinel query
DeviceFileEvents
| where TimeGenerated > ago(30d)
| where ActionType == "FileCreated"
//When an ISO file is mounted a .iso.lnk file is created, take that name and trim the .lnk out to retrieve the ISO name
| where FileName endswith "iso.lnk"
| extend ['ISO FileName'] = trim(@".lnk",FileName)
//Summarize and visualize the files
| summarize Count=count() by ['ISO FileName']
| top 20 by Count
| render barchart with (title="Most common ISO files being mounted")

//Advanced Hunting query

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

DeviceFileEvents
| where Timestamp > ago(30d)
| where ActionType == "FileCreated"
//When an ISO file is mounted a .iso.lnk file is created, take that name and trim the .lnk out to retrieve the ISO name
| where FileName endswith "iso.lnk"
| extend ['ISO FileName'] = trim(@".lnk",FileName)
//Summarize and visualize the files
| summarize Count=count() by ['ISO FileName']
| top 20 by Count
| render columnchart

//Visualize the OS build numbers of your Windows 10 and 11 devices per month over the last year

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

DeviceInfo
| where TimeGenerated > ago(365d)
| where OSPlatform in ("Windows10", "Windows11")
| extend OSBuildNumber=tostring(OSBuild)
| summarize arg_max(TimeGenerated, *) by DeviceName, startofmonth(TimeGenerated)
| summarize count()by OSBuildNumber, startofmonth(TimeGenerated)
| where isnotempty(OSBuildNumber)
| render areachart 
    with (
    ytitle="Device Count",
    xtitle="Month",
    title="Count of Windows 10 and 11 OS Builds per month")

//Summarize and visualize the different parent filenames initiating TCP port 22 connections

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

//Microsoft Sentinel query
DeviceNetworkEvents
| where TimeGenerated > ago(30d)
| where ActionType == "ConnectionSuccess"
| where RemotePort == 22
| where isnotempty(InitiatingProcessFileName)
| summarize Count=count() by InitiatingProcessFileName
| top 20 by Count
| render barchart with (title="Proccesses initiating TCP port 22 connections")

//Advanced Hunting query

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

DeviceNetworkEvents
| where Timestamp > ago(30d)
| where ActionType == "ConnectionSuccess"
| where RemotePort == 22
| where isnotempty(InitiatingProcessFileName)
| summarize Count=count() by InitiatingProcessFileName
| top 20 by Count
//Advanced hunting doesn't support barcharts, so can render as a piechart or just remove the line below for a table
| render piechart

//Visualize the different RDP clients, such as rMemoteNG or RoyalTS being used in your environment

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

//Microsoft Sentinel query
DeviceNetworkEvents
| where TimeGenerated > ago(7d)
| where ActionType == "ConnectionSuccess"
| where RemotePort == "3389"
//Exclude Defender for Identity which uses RDP traffic to map your network
| where InitiatingProcessFileName != "Microsoft.Tri.Sensor.exe"
| summarize ['RDP Client Count']=count()by InitiatingProcessFileName
| where isnotempty(InitiatingProcessFileName)
| sort by ['RDP Client Count'] desc
| render barchart 

//Advanced Hunting query

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

DeviceNetworkEvents
| where Timestamp > ago(7d)
| where ActionType == "ConnectionSuccess"
| where RemotePort == "3389"
//Exclude Defender for Identity which uses RDP traffic to map your network
| where InitiatingProcessFileName != "Microsoft.Tri.Sensor.exe"
| summarize ['RDP Client Count']=count()by InitiatingProcessFileName
| where isnotempty(InitiatingProcessFileName)
| sort by ['RDP Client Count'] desc
| render barchart

//Visualize the top 20 remote URLs that your users are connecting to via PowerShell

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

//Microsoft Sentinel query
DeviceNetworkEvents
| where TimeGenerated > ago (7d)
//Exclude system and local service processes as this visualization is user focused
| where InitiatingProcessAccountName !in~ ("system", "local service")
| where InitiatingProcessCommandLine has "powershell"
| where LocalIPType == "Private"
| where RemoteIPType == "Public"
| summarize Count=count()by RemoteUrl
| where isnotempty(RemoteUrl)
| top 20 by Count
| render barchart with (title="Remote URLs accessed by Powershell")

//Advanced Hunting query

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

DeviceNetworkEvents
| where Timestamp > ago (7d)
//Exclude system and local service processes as this visualization is user focused
| where InitiatingProcessAccountName !in~ ("system", "local service")
| where InitiatingProcessCommandLine has "powershell"
| where LocalIPType == "Private"
| where RemoteIPType == "Public"
| summarize Count=count()by RemoteUrl
| where isnotempty(RemoteUrl)
| top 20 by Count
//Advanced Hunting does not support barcharts so you can visualize as a piechart or simply remove this line for a table
| render piechart with (title="Remote URLs accessed by Powershell")

//Visualize how much data is being copied to USB drives per day in your environment over the time range.

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

DeviceEvents
| where TimeGenerated > ago (21d)
| project TimeGenerated, ActionType, AdditionalFields, DeviceId, FileName
| where ActionType == "UsbDriveMounted"
| extend DriveLetter = tostring(todynamic(AdditionalFields).DriveLetter)
| join kind=inner (DeviceFileEvents
    | where TimeGenerated > ago (21d)
    | project TimeGenerated, ActionType, FolderPath, DeviceId, FileName, FileSize
    | extend FileCopyTime = TimeGenerated
    | where ActionType == "FileCreated"
    | parse FolderPath with DriveLetter '\\' *
    | extend DriveLetter = tostring(DriveLetter)
    )
    on DeviceId, DriveLetter
| distinct FileCopyTime, FileName1, FileSize
| summarize DataCopiedinGB=sum(FileSize / 1024 / 1024 / 1024) by startofday(FileCopyTime)
| render columnchart
    with (
    kind=unstacked,
    xtitle="Data Copied in GB",
    ytitle="Day",
    title="Data Copied to USB per day")

//Finds Windows 11 devices enrolled in Defender for Endpoint and the last user who logged on interactively

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

DeviceInfo
| where TimeGenerated > ago(60d)
| where isnotempty( OSPlatform)
| summarize arg_max(TimeGenerated, *) by DeviceName
| extend OSBuildString = tostring(OSBuild)
| where OSPlatform == "Windows11" or OSBuildString startswith "22" or OSBuildString startswith "21"
| project DeviceName, OSBuild, OSPlatform, OSVersion
|join kind=inner (
DeviceLogonEvents
| where LogonType == "Interactive"
| where ActionType == "LogonSuccess"
| where InitiatingProcessCommandLine == "lsass.exe"
| summarize arg_max(TimeGenerated, *) by DeviceName
) on DeviceName
| project DeviceName, AccountName, OSBuild, OSPlatform, OSVersion

//Create a pivot table of all Windows OS versions in your environment

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

//Microsoft Sentinel query
DeviceInfo
| where TimeGenerated > ago(30d)
| where isnotempty(OSBuild)
| summarize arg_max(TimeGenerated, *) by DeviceId
| where isnotempty(OSPlatform)
| evaluate pivot(OSBuild, count(), OSPlatform)
| where OSPlatform contains "Windows"
| sort by OSPlatform desc 

//Advanced Hunting query

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

DeviceInfo
| where Timestamp > ago(30d)
| where isnotempty(OSBuild)
| summarize arg_max(Timestamp, *) by DeviceName
| where isnotempty(OSPlatform)
| evaluate pivot(OSBuild, count(), OSPlatform)
| where OSPlatform contains "Windows"
| sort by OSPlatform desc

//Detections based on the emerging information found here - https://twitter.com/nao_sec/status/1530196847679401984, https://twitter.com/GossiTheDog/status/1531018365606707206 and https://doublepulsar.com/follina-a-microsoft-office-code-execution-vulnerability-1a47fce5629e
//Microsoft Sentinel queries
//Search your device process events for msdt.exe being generated by Outlook, Word or Excel - should be low noise and high value alerts, seems very rare behaviour
DeviceProcessEvents
| where ProcessCommandLine contains "msdt.exe" and InitiatingProcessCommandLine has_any ("outlook.exe", "winword.exe", "excel.exe") 

//Search your device process events for msdt.exe spawning processes other than itself
DeviceProcessEvents
| where InitiatingProcessCommandLine contains "msdt.exe" and ProcessCommandLine !contains "msdt.exe"

//Likely to get false positives with msdt.exe spawning a process other than itself, so instead look for new events seen today for the first time based on distinct process and parent process
DeviceProcessEvents
| where TimeGenerated > ago (30d) and TimeGenerated < ago(1d)
| project InitiatingProcessCommandLine, ProcessCommandLine
| where InitiatingProcessCommandLine contains "msdt.exe" and ProcessCommandLine !contains "msdt.exe"
| distinct InitiatingProcessCommandLine, ProcessCommandLine
| join kind=rightanti 
    (
    DeviceProcessEvents
    | where TimeGenerated > ago (1d)
    | where InitiatingProcessCommandLine contains "msdt.exe" and ProcessCommandLine !contains "msdt.exe"
    )
 on InitiatingProcessCommandLine, ProcessCommandLine
 | project TimeGenerated, DeviceName, InitiatingProcessAccountName, InitiatingProcessCommandLine, ProcessCommandLine

//Look for new public connections from "sdiagnhost.exe" or "msdt.exe" as per https://twitter.com/MalwareJake/status/1531088843792957442
//"sdiagnhost.exe" legimitately connects to some internet endpoints as part of Microsoft telemetry so find events new to today to investigate
let knownips=
    DeviceNetworkEvents
    | where TimeGenerated > ago(30d) and TimeGenerated < ago(1d)
    | where InitiatingProcessFileName has_any ("sdiagnhost.exe", "msdt.exe")
    | where RemoteIPType == "Public"
    | distinct RemoteIP;
DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where InitiatingProcessFileName has_any ("sdiagnhost.exe", "msdt.exe")
| where RemoteIPType == "Public"
| where RemoteIP !in (knownips)
| project
    TimeGenerated,
    ActionType,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessFileName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemotePort,
    RemoteUrl



//Advanced Hunting queries
//Search your device process events for msdt.exe being generated by Outlook, Word or Excel - should be low noise and high value alerts, seems very rare behaviour
DeviceProcessEvents
| where ProcessCommandLine contains "msdt.exe" and InitiatingProcessCommandLine has_any ("outlook.exe", "winword.exe", "excel.exe") 

//Search your device process events for msdt.exe spawning processes other than itself
DeviceProcessEvents
| where InitiatingProcessCommandLine contains "msdt.exe" and ProcessCommandLine !contains "msdt.exe"

//Likely to get false positives with msdt.exe spawning a process other than itself, so instead look for new events seen today for the first time based on distinct process and parent process
DeviceProcessEvents
| where Timestamp > ago (30d) and Timestamp < ago(1d)
| project InitiatingProcessCommandLine, ProcessCommandLine
| where InitiatingProcessCommandLine contains "msdt.exe" and ProcessCommandLine !contains "msdt.exe"
| distinct InitiatingProcessCommandLine, ProcessCommandLine
| join kind=rightanti 
    (
    DeviceProcessEvents
    | where Timestamp > ago (1d)
    | where InitiatingProcessCommandLine contains "msdt.exe" and ProcessCommandLine !contains "msdt.exe"
    )
 on InitiatingProcessCommandLine, ProcessCommandLine
 | project Timestamp, DeviceName, InitiatingProcessAccountName, InitiatingProcessCommandLine, ProcessCommandLine

//Look for new public connections from "sdiagnhost.exe" or "msdt.exe" as per https://twitter.com/MalwareJake/status/1531088843792957442
//"sdiagnhost.exe" legimitately connects to some internet endpoints as part of Microsoft telemetry so find events new to today to investigate
let knownips=
    DeviceNetworkEvents
    | where Timestamp > ago(30d) and Timestamp < ago(1d)
    | where InitiatingProcessFileName has_any ("sdiagnhost.exe", "msdt.exe")
    | where RemoteIPType == "Public"
    | distinct RemoteIP;
DeviceNetworkEvents
| where Timestamp > ago(1d)
| where InitiatingProcessFileName has_any ("sdiagnhost.exe", "msdt.exe")
| where RemoteIPType == "Public"
| where RemoteIP !in (knownips)
| where RemoteUrl !endswith ".visualstudio.com" and RemoteUrl !endswith ".microsoft.com"
| project
    Timestamp,
    ActionType,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessFileName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemotePort,
    RemoteUrl

//Find devices that have had no inbound web connections in the last 30 days to help build firewall policy

//Microsoft Sentinel query

let devices=
DeviceNetworkEvents
| where TimeGenerated > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort in ("80","443","8080")
| distinct DeviceId;
DeviceInfo
| where TimeGenerated > ago (30d)
| distinct DeviceId, DeviceName
| where DeviceId !in (devices)


//Advanced Hunting query

let devices=
DeviceNetworkEvents
| where Timestamp > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort in ("80","443","8080")
| distinct DeviceId;
DeviceInfo
| where Timestamp > ago (30d)
| distinct DeviceId, DeviceName
| where DeviceId !in (devices)

//Find devices that have had no inbound RDP connections in the last 30 days to help build firewall policy

//Microsoft Sentinel query

let devices=
DeviceNetworkEvents
| where TimeGenerated > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort == 3389
| distinct DeviceId;
DeviceInfo
| where TimeGenerated > ago (30d)
| distinct DeviceId, DeviceName
| where DeviceId !in (devices)


//Advanced Hunting query

let devices=
DeviceNetworkEvents
| where Timestamp > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort == 3389
| distinct DeviceId;
DeviceInfo
| where Timestamp > ago (30d)
| distinct DeviceId, DeviceName
| where DeviceId !in (devices)

//Find devices that have had no inbound SMB connections in the last 30 days to help build firewall policy

//Microsoft Sentinel query

let devices=
DeviceNetworkEvents
| where TimeGenerated > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort in ("139","445")
| distinct DeviceId;
DeviceInfo
| where TimeGenerated > ago (30d)
| distinct DeviceId, DeviceName
| where DeviceId !in (devices)


//Advanced Hunting query

let devices=
DeviceNetworkEvents
| where Timestamp > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort in ("139","445")
| distinct DeviceId;
DeviceInfo
| where Timestamp > ago (30d)
| distinct DeviceId, DeviceName
| where DeviceId !in (devices)

//Find devices that have had no inbound SSH connections in the last 30 days to help build firewall policy

//Microsoft Sentinel query

let devices=
DeviceNetworkEvents
| where TimeGenerated > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort == 22
| distinct DeviceId;
DeviceInfo
| where TimeGenerated > ago (30d)
| distinct DeviceId, DeviceName
| where DeviceId !in (devices)


//Advanced Hunting query

let devices=
DeviceNetworkEvents
| where Timestamp > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort == 22
| distinct DeviceId;
DeviceInfo
| where Timestamp > ago (30d)
| distinct DeviceId, DeviceName
| where DeviceId !in (devices)

//Summary of inbound traffic, will find the total count, distcount count of devices and the list of inbound devices per port

//Microsoft Sentinel query

DeviceNetworkEvents
| where TimeGenerated > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort in ("22","80","139","443","445","3389","8080")
| summarize ['Total Count']=count(), ['Distinct Count of Inbound Devices']=dcount(RemoteIP), ['List of Inbound Devices']=make_set(RemoteIP) by DeviceName, LocalPort
| sort by DeviceName asc, LocalPort asc 

//Advanced Hunting query

//Summary of inbound traffic, will find the total count, distcount count of devices and the list of inbound devices per port
DeviceNetworkEvents
| where Timestamp > ago (30d)
| where ActionType == "InboundConnectionAccepted"
| where LocalPort in ("22","80","139","443","445","3389","8080")
| summarize ['Total Count']=count(), ['Distinct Count of Inbound Devices']=dcount(RemoteIP), ['List of Inbound Devices']=make_set(RemoteIP) by DeviceName, LocalPort
| sort by DeviceName asc, LocalPort asc

let discord=dynamic(["cdn.discordapp.com", "media.discordapp.com"]);
  _Im_WebSession(url_has_any=discord, eventresult='Success')
  | where Url has "attachments"
  | extend DiscordServerId = extract(@"\/attachments\/([0-9]+)\/", 1, Url)
  | summarize dcount(Url), make_set(SrcUsername), make_set(SrcIpAddr), make_set(Url), min(TimeGenerated), max(TimeGenerated), make_set(EventResult) by DiscordServerId
  | mv-expand set_SrcUsername to typeof(string), set_Url to typeof(string), set_EventResult to typeof(string), set_SrcIpAddr to typeof(string)
  | summarize by DiscordServerId, dcount_Url, set_SrcUsername, min_TimeGenerated, max_TimeGenerated, set_EventResult, set_SrcIpAddr, set_Url
  | project StartTime=min_TimeGenerated, EndTime=max_TimeGenerated, Result=set_EventResult, SourceUser=set_SrcUsername, SourceIP=set_SrcIpAddr, RequestURL=set_Url
  | where RequestURL has_any (".bin",".exe",".dll",".bin",".msi")
  | extend AccountName = tostring(split(SourceUser, "@")[0]), AccountUPNSuffix = tostring(split(SourceUser, "@")[1])

//Find DNS domains that have not been queried in the last 30 days. These are potentially stale and should be removed.

//Data connector required for this query - DNS

let domain="yourdomain.com";
DnsEvents
| where TimeGenerated > ago(180d)
| where SubType == "LookupQuery"
| where QueryType == "A"
| where Name endswith domain
| summarize LookupCount=count()by Name
//Set a threshold for total lookups to be included, to account for typos and low volume queries
| where LookupCount > 50
| join kind=leftanti 
    (
    DnsEvents
    | where TimeGenerated > ago(30d)
    | where SubType == "LookupQuery"
    | where QueryType == "A"
    | where Name endswith domain
    | summarize arg_max(TimeGenerated, Name) by Name
    | project TimeGenerated, Name)
    on Name

//Parser for Duo data sent to a custom table and join to identity info to correlate legacy usernames with userprincipalname
let id=
    IdentityInfo
    | where TimeGenerated > ago(21d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | extend DuoUserName = AccountName
    | project AccountUPN, DuoUserName;
DuoLogs_CL
| where TimeGenerated > ago(1d)
| extend logs = split(SyslogMessage_s, "|")
| extend vendor = logs[1]
| extend app = logs[2]
| extend version = logs[3]
| extend event = logs[4]
| extend msg = (logs[5])
| where event == "authentication"
| extend DuoTime = EventTime_t
| extend DuoApplication = extract("cs1=(.*?) c", 1, SyslogMessage_s)
| extend DuoIPAddr = extract("src=(.*?) c", 1, SyslogMessage_s)
| extend DuoMethod = extract("cs3=(.*?) o", 1, SyslogMessage_s)
| extend DuoOutcome = extract("outcome=(.*?) r", 1, SyslogMessage_s)
| parse SyslogMessage_s with * "duser=" DuoUserName
| join kind=inner id on DuoUserName
| project
    DuoTime,
    DuoUserName,
    AccountUPN,
    DuoIPAddr,
    DuoApplication,
    DuoOutcome,
    DuoMethod
| sort by DuoTime desc

let timeframe = 1d;
let cmdList = dynamic(["Set-CASMailbox","ActiveSyncAllowedDeviceIDs","add"]);
(union isfuzzy=true
(
SecurityEvent
| where TimeGenerated >= ago(timeframe)
| where EventID == 4688
| where CommandLine has_all (cmdList)
| project Type, TimeGenerated, Computer, Account, SubjectDomainName, SubjectUserName, Process, ParentProcessName, CommandLine
| extend timestamp = TimeGenerated, AccountEntity = Account, HostEntity = Computer
),
( WindowsEvent
| where TimeGenerated >= ago(timeframe)
| where EventID == 4688
| where EventData has_all (cmdList)
| extend CommandLine = tostring(EventData.CommandLine) 
| where CommandLine has_all (cmdList)
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend SubjectUserName = tostring(EventData.SubjectUserName)
| extend SubjectDomainName = tostring(EventData.SubjectDomainName)
| extend NewProcessName = tostring(EventData.NewProcessName)
| extend Process=tostring(split(NewProcessName, '\\')[-1])
| extend ParentProcessName = tostring(EventData.ParentProcessName)
| project Type, TimeGenerated, Computer, Account, SubjectDomainName, SubjectUserName, Process, ParentProcessName, CommandLine
| extend timestamp = TimeGenerated, AccountEntity = Account, HostEntity = Computer
),
(
DeviceProcessEvents
| where TimeGenerated >= ago(timeframe)
| where InitiatingProcessCommandLine has_all (cmdList)
| project Type, TimeGenerated, DeviceName, AccountName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessFileName, InitiatingProcessParentFileName,  InitiatingProcessCommandLine
| extend timestamp = TimeGenerated, AccountDomain = InitiatingProcessAccountDomain, AccountName = InitiatingProcessAccountName, HostEntity = DeviceName
),
(
Event
| where TimeGenerated > ago(timeframe)
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 1
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key=tostring(['@Name']), Value=['#text']
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)
| where TimeGenerated >= ago(timeframe)
| where CommandLine has_all (cmdList)
| extend Type = strcat(Type, ": ", Source)
| project Type, TimeGenerated, Computer, User, Process, ParentImage, CommandLine
| extend timestamp = TimeGenerated, AccountEntity = User, HostEntity = Computer
)
)
| extend HostName = tostring(split(HostEntity, ".")[0]), DomainIndex = toint(indexof(HostEntity, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(HostEntity, DomainIndex + 1), HostEntity)
| extend AccountName = tostring(split(AccountEntity, @'\')[1]), AccountDomain = tostring(split(AccountEntity, @'\')[0])

//When Defender for Cloud detects a possible DNS lookup to a phishing domain attempt to find if the URL was part of an email phishing attack

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

let suspiciousdomains=
    SecurityAlert
    | where AlertName startswith "Communication with possible phishing domain"
    | mv-expand todynamic(Entities)
    | extend DomainName = tostring(Entities.DomainName)
    | where isnotempty(DomainName)
    | distinct DomainName;
EmailEvents
| where EmailDirection == "Inbound"
| project
    TimeGenerated,
    SenderMailFromAddress,
    RecipientEmailAddress,
    EmailDirection,
    NetworkMessageId
| join kind=inner (EmailUrlInfo) on NetworkMessageId
| project
    TimeGenerated,
    SenderMailFromAddress,
    RecipientEmailAddress,
    EmailDirection,
    Url,
    UrlDomain
| where UrlDomain in~ (suspiciousdomains)

//When a malicious email is received, list all the users who received it and all the users who read it

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

//First find all the users who received the email
EmailEvents
| where EmailDirection == "Inbound"
//Add in your malicious subject or malicious sender address
| where Subject == "Malicious Subject Name" or SenderFromAddress == "[email protected]"
| project RecipientEmailAddress, Subject, InternetMessageId, SenderFromAddress
//Combine that data with the OfficeActivity table which tracks whether an email was accessed
| join kind=inner (
    OfficeActivity
    | where Operation == "MailItemsAccessed"
    | mv-expand todynamic(Folders)
    | extend FolderItems = Folders.FolderItems
    | mv-expand FolderItems
    | extend InternetMessageId = tostring(FolderItems.InternetMessageId)
    | project UserId, InternetMessageId
    )
    on InternetMessageId
//Create summary of data listing the count and all the users who received the email vs the list and count of those that read it
| summarize
    ['Receipient Count']=dcount(RecipientEmailAddress),
    ['Users Who Received Email']=make_set(RecipientEmailAddress),
    ['Reader Count']=dcount(UserId),
    ['Users Who Read Email']=make_set(UserId)
    by SenderFromAddress, Subject, InternetMessageId

//When a macro is received via email from an external sender, find all users who receieved the same file

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

//Microsoft Sentinel query
EmailEvents
| where EmailDirection == "Inbound"
| project
    TimeGenerated,
    SenderMailFromAddress,
    RecipientEmailAddress,
    NetworkMessageId
| join kind=inner (EmailAttachmentInfo) on NetworkMessageId
| project
    TimeGenerated,
    SenderMailFromAddress,
    RecipientEmailAddress,
    FileName
| where FileName endswith ".xlsm" or FileName endswith ".xstm"
| summarize Recipient=make_set(RecipientEmailAddress) by FileName, SenderMailFromAddress

//Advanced Hunting query

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

EmailEvents
| where EmailDirection == "Inbound"
| project
    Timestamp,
    SenderMailFromAddress,
    RecipientEmailAddress,
    NetworkMessageId
| join kind=inner (EmailAttachmentInfo) on NetworkMessageId
| project
    Timestamp,
    SenderMailFromAddress,
    RecipientEmailAddress,
    FileName
| where FileName endswith ".xlsm" or FileName endswith ".xstm"
| summarize Recipient=make_set(RecipientEmailAddress) by FileName, SenderMailFromAddress

//Visualize the most blocked domains sending email inbound to your users

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

//Microsoft Sentinel query
EmailEvents
| where TimeGenerated > ago (7d)
| where EmailDirection == "Inbound"
| where DeliveryAction == "Blocked"
| extend Domain = tostring(split(SenderMailFromAddress, "@")[-1])
| summarize BlockedCount=count()by Domain
| where isnotempty(Domain)
| sort by BlockedCount desc
| render barchart 

//Advanced Hunting query

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

EmailEvents
| where Timestamp > ago (7d)
| where EmailDirection == "Inbound"
| where DeliveryAction == "Blocked"
| extend Domain = tostring(split(SenderMailFromAddress, "@")[-1])
| summarize BlockedCount=count()by Domain
| where isnotempty(Domain)
| sort by BlockedCount desc
| render barchart

//Find senders that are potentially spamming your users for the first time. Useful at detecting business email compromise from partner companies.

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

//Set a threshold of the same email being received within a 10 minute period
//Microsoft Sentinel query
let threshold = 500;
//First create a list of sender addresses that have previously sent you bulk email, hopefully this will let us exclude corporate communications and newsletters etc
let knownbulkemail=
    EmailEvents
    | where TimeGenerated > ago(30d) and TimeGenerated < ago (1d)
    | project TimeGenerated, EmailDirection, DeliveryAction, RecipientEmailAddress, SenderFromAddress, Subject
    | where EmailDirection == "Inbound"
    | where DeliveryAction == "Delivered"
    | summarize RecipientCount=dcount(RecipientEmailAddress) by SenderFromAddress, Subject, bin(TimeGenerated, 10m)
    | where RecipientCount > threshold
    | distinct SenderFromAddress;
//Look in the last hour for any new senders that have sent over the threshold in a 10 minute period
EmailEvents
| where TimeGenerated > ago(1d)
| project TimeGenerated, EmailDirection, DeliveryAction, RecipientEmailAddress, SenderFromAddress, Subject
| where EmailDirection == "Inbound"
| where DeliveryAction == "Delivered"
| summarize RecipientCount=dcount(RecipientEmailAddress) by SenderFromAddress, Subject, bin(TimeGenerated, 10m)
| where SenderFromAddress !in (knownbulkemail) and RecipientCount > threshold

//Advanced Hunting query

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

let threshold = 500;
//First create a list of sender addresses that have previously sent you bulk email, hopefully this will let us exclude corporate communications and newsletters etc
let knownbulkemail=
    EmailEvents
    | where Timestamp > ago(30d) and Timestamp < ago (1d)
    | project Timestamp, EmailDirection, DeliveryAction, RecipientEmailAddress, SenderFromAddress, Subject
    | where EmailDirection == "Inbound"
    | where DeliveryAction == "Delivered"
    | summarize RecipientCount=dcount(RecipientEmailAddress) by SenderFromAddress, Subject, bin(Timestamp, 10m)
    | where RecipientCount > threshold
    | distinct SenderFromAddress;
//Look in the last hour for any new senders that have sent over the threshold in a 10 minute period
EmailEvents
| where Timestamp > ago(1d)
| project Timestamp, EmailDirection, DeliveryAction, RecipientEmailAddress, SenderFromAddress, Subject
| where EmailDirection == "Inbound"
| where DeliveryAction == "Delivered"
| summarize RecipientCount=dcount(RecipientEmailAddress) by SenderFromAddress, Subject, bin(Timestamp, 10m)
| where SenderFromAddress !in (knownbulkemail) and RecipientCount > threshold

//Visualize the deviation of email being blocked to your Office 365 tenant per day
//Query adapted from https://github.com/samikroy/kql-store/blob/main/Deviation%20in%20Security%20Events.md

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

//Find the average blocked email per day
let AverageBlockedEmail = toscalar(EmailEvents
| where TimeGenerated > ago(250d)
| where DeliveryAction == "Blocked"
| summarize Count=count() by bin(TimeGenerated, 1d)
| summarize avg(Count));
//Find the total count of blocked email per day
EmailEvents
| where TimeGenerated > ago(250d)
| where DeliveryAction == "Blocked"
| summarize Count=count() by bin(TimeGenerated, 1d)
| extend Deviation = (Count - AverageBlockedEmail) / AverageBlockedEmail
| project-away Count
//Visualize the deviation per day
| render columnchart with (title="Deviation of email blocked per day")

//Visualize how much is email is being blocked as a percentage of total email over time

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

EmailEvents
| where TimeGenerated > ago (30d)
| where EmailDirection == "Inbound"
| summarize
    TotalCount=count(),
    BlockedCount=countif(DeliveryAction in ("Blocked", "Junked"))
    by bin(TimeGenerated, 6h)
| extend Percentage=(todouble(BlockedCount) * 100 / todouble(TotalCount))
| project-away TotalCount, BlockedCount
| render timechart with (title="Percentage of email blocked over time", ymax=100)

//Visualize inbound email actions (Delivered, Junked, Blocked) per day over time

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

//Microsoft Sentinel query
EmailEvents
| where TimeGenerated > ago (90d)
| where EmailDirection == "Inbound"
| summarize Count=count()by DeliveryAction, bin(TimeGenerated, 1d)
| render columnchart with (kind=unstacked, title="Email delivery actions over time")

//Advanced Hunting query. Advanced hunting only retains 30 days data, so to show a similar visualization, we can slice the vents up into 6 hour blocks

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

EmailEvents
| where Timestamp > ago (30d)
| where EmailDirection == "Inbound"
| summarize count()by DeliveryAction, bin(Timestamp, 6h)
//Advanced hunting cannot visualize column charts as well as Sentinel so rendering as a timechart produces a better result
| render timechart

//Visualize any post delivery actions such as email being quarantined or deleted by admins

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

EmailPostDeliveryEvents
| where TimeGenerated > ago (90d)
| where Action !in ("None", "Unknown")
| make-series Count=count() on TimeGenerated from ago(45d) to now() step 1d by Action
| render timechart with (title="Email post delivery actions over time")

AuditLogs
  | where OperationName has "Consent to application"
  | where Result =~ "failure"
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend userAgent = iif(AdditionalDetails[0].key == "User-Agent", tostring(AdditionalDetails[0].value), tostring(AdditionalDetails[1].value))
  | where isnotempty(TargetResources)
  | extend TargetAppName = tostring(TargetResources[0].displayName)
  | extend TargetAppId = tostring(TargetResources[0].id)
  | mv-expand TargetResources[0].modifiedProperties
  | extend TargetResources_0_modifiedProperties = columnifexists("TargetResources_0_modifiedProperties", '')
  | where isnotempty(TargetResources_0_modifiedProperties)
  | where TargetResources_0_modifiedProperties.displayName =~ "MethodExecutionResult."
  | extend TargetPropertyDisplayName = tostring(TargetResources_0_modifiedProperties.displayName)
  | extend FailureReason = tostring(parse_json(tostring(TargetResources_0_modifiedProperties.newValue)))
  | where FailureReason contains "Risky"
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, OperationName, Result, TargetAppName, TargetAppId, FailureReason, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress, userAgent

let iocs = externaldata(DateAdded:string,IoC:string,Type:string,TLP:string) [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/Europium_September2022.csv"] with (format="csv", ignoreFirstRecord=True);
let sha256Hashes = (iocs | where Type =~ "sha256" | project IoC);
let IPList = (iocs | where Type =~ "ip"| project IoC);
let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
(union isfuzzy=true
(CommonSecurityLog
| where SourceIP in (IPList) or DestinationIP in (IPList) or Message has_any (IPList)
| parse Message with * '(' DNSName ')' * 
| project TimeGenerated, SourceIP, DestinationIP, Message, SourceUserID, RequestURL, DNSName, Type
| extend MessageIP = extract(IPRegex, 0, Message), RequestIP = extract(IPRegex, 0, RequestURL)
| extend IPMatch = case(SourceIP in (IPList), "SourceIP", DestinationIP in (IPList), "DestinationIP", MessageIP in (IPList), "Message",  "NoMatch")
| extend timestamp = TimeGenerated, IPEntity = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, IPMatch == "Message", MessageIP, "NoMatch")
),
(DnsEvents
| where IPAddresses in (IPList)  
| project TimeGenerated, Computer, IPAddresses, Name, ClientIP, Type
| extend DestinationIPAddress = IPAddresses, DNSName = Name, Computer 
| extend timestamp = TimeGenerated, IPEntity = DestinationIPAddress, HostEntity = Computer
),
(VMConnection
| where SourceIp in (IPList) or DestinationIp in (IPList)
| parse RemoteDnsCanonicalNames with * '["' DNSName '"]' *
| project TimeGenerated, Computer, Direction, ProcessName, SourceIp, DestinationIp, DestinationPort, RemoteDnsQuestions, DNSName,BytesSent, BytesReceived, RemoteCountry, Type
| extend IPMatch = case( SourceIp in (IPList), "SourceIP", DestinationIp in (IPList), "DestinationIP", "None") 
| extend timestamp = TimeGenerated, IPEntity = case(IPMatch == "SourceIP", SourceIp, IPMatch == "DestinationIP", DestinationIp, "NoMatch"), File = ProcessName, HostEntity = Computer
),
(Event
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 3
| extend EvData = parse_xml(EventData)
| extend EventDetail = EvData.DataItem.EventData.Data
| extend SourceIP = tostring(EventDetail.[9].["#text"]), DestinationIP = tostring(EventDetail.[14].["#text"]), Image = tostring(EventDetail.[4].["#text"])
| where SourceIP in (IPList) or DestinationIP in (IPList)
| project TimeGenerated, SourceIP, DestinationIP, Image, UserName, Computer, Type
| extend IPMatch = case( SourceIP in (IPList), "SourceIP", DestinationIP in (IPList), "DestinationIP", "None")
| extend timestamp = TimeGenerated, File = tostring(split(Image, '\\', -1)[-1]), IPEntity = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, "None"), 
HostEntity = Computer, AccountName = tostring(split(UserName, @'\')[1]), AccountDomain = tostring(split(UserName, @'\')[0])
| extend InitiatingProcessAccount = UserName
), 
(OfficeActivity
| where ClientIP in (IPList) 
| project TimeGenerated, UserAgent, Operation, RecordType, UserId, ClientIP, Type
| extend timestamp = TimeGenerated, IPEntity = ClientIP, AccountName = tostring(split(UserId, "@")[0]), AccountDomain = tostring(split(UserId, "@")[1])
| extend InitiatingProcessAccount = UserId
),
(DeviceNetworkEvents
| where RemoteIP in (IPList) or InitiatingProcessSHA256 in (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, Computer = DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, 
InitiatingProcessFolderPath, InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, RemoteIP, RemoteUrl, RemotePort, LocalIP, Type
| extend timestamp = TimeGenerated, IPEntity = RemoteIP, HostEntity = Computer, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain
| extend InitiatingProcessAccount = strcat(AccountDomain, "\\", AccountName)
),
(WindowsFirewall
| where SourceIP in (IPList) or DestinationIP in (IPList) 
| project TimeGenerated, Computer, CommunicationDirection, SourceIP, DestinationIP, SourcePort, DestinationPort, Type
| extend IPMatch = case( SourceIP in (IPList), "SourceIP", DestinationIP in (IPList), "DestinationIP", "None")
| extend timestamp = TimeGenerated, HostEntity = Computer, IPEntity = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, "None")
), 
(imFileEvent
| where TargetFileSHA256 has_any (sha256Hashes)
| extend Account = ActorUsername, Computer = DvcHostname, IPAddress = SrcIpAddr, CommandLine = ActingProcessCommandLine, FileHash = TargetFileSHA256
| project Type, TimeGenerated, Computer, Account, IPAddress, CommandLine, FileHash
| extend timestamp = TimeGenerated, IPEntity = IPAddress,  HostEntity = Computer, Algorithm = "SHA256", FileHash = tostring(FileHash)
| extend AccountName = tostring(split(Account, @'\')[1]), AccountDomain = tostring(split(Account, @'\')[0])
| extend InitiatingProcessAccount = Account
),
(DeviceFileEvents
| where SHA256 has_any (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, 
InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, InitiatingProcessSHA256, Type
| extend timestamp = TimeGenerated, HostEntity = DeviceName, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain, 
Algorithm = "SHA256", FileHash = tostring(InitiatingProcessSHA256), CommandLine = InitiatingProcessCommandLine,Image = InitiatingProcessFolderPath
| extend InitiatingProcessAccount = strcat(AccountDomain, "\\", AccountName)
),
(DeviceImageLoadEvents
| where SHA256 has_any (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, 
InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, InitiatingProcessSHA256, Type
| extend timestamp = TimeGenerated, HostEntity = DeviceName, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain, 
Algorithm = "SHA256", FileHash = tostring(InitiatingProcessSHA256),  CommandLine = InitiatingProcessCommandLine,Image = InitiatingProcessFolderPath
| extend InitiatingProcessAccount = strcat(AccountDomain, "\\", AccountName)
),
(Event
| where Source =~ "Microsoft-Windows-Sysmon"
| where EventID == 1
| extend EvData = parse_xml(EventData)
| extend EventDetail = EvData.DataItem.EventData.Data
| extend Image = EventDetail.[4].["#text"],  CommandLine = EventDetail.[10].["#text"], Hashes = tostring(EventDetail.[17].["#text"])
| extend Hashes = extract_all(@"(?P<key>\w+)=(?P<value>[a-zA-Z0-9]+)", dynamic(["key","value"]), Hashes)
| extend Hashes = column_ifexists("Hashes", dynamic(["", ""])), CommandLine = column_ifexists("CommandLine", "")
| mv-expand Hashes
| where Hashes[0] =~ "SHA256" and Hashes[1] has_any (sha256Hashes)  
| project TimeGenerated, EventDetail, UserName, Computer, Type, Source, Hashes, CommandLine, Image
| extend Type = strcat(Type, ": ", Source)
| extend timestamp = TimeGenerated, HostEntity = Computer, AccountName = tostring(split(UserName, @'\')[1]), AccountUPNSuffix = tostring(split(UserName, @'\')[0]), FileHash = tostring(Hashes[1])
| extend InitiatingProcessAccount = UserName
)
)
| extend HostName = tostring(split(HostEntity, ".")[0]), DomainIndex = toint(indexof(HostEntity, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(HostEntity, DomainIndex + 1), HostEntity)

let threshold = 200;
_Im_Dns(responsecodename='NXDOMAIN')
| where isnotempty(DnsResponseCodeName)
//| where DnsResponseCodeName =~ "NXDOMAIN"
| summarize count() by SrcIpAddr, bin(TimeGenerated,15m)
| where count_ > threshold
| join kind=inner (_Im_Dns(responsecodename='NXDOMAIN')
    ) on SrcIpAddr

let error403_count_threshold=200;
_Im_WebSession(eventresultdetails_in=dynamic(["403"]))
| extend ParsedUrl=parse_url(Url)
| extend UrlHost=tostring(ParsedUrl["Host"]), UrlSchema=tostring(ParsedUrl["Schema"])
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), NumberOfErrors = count(), Urls=makeset(Url) by UrlHost, SrcIpAddr
| where NumberOfErrors > error403_count_threshold
| sort by NumberOfErrors desc
| extend Url=tostring(Urls[0])

let successCodes = dynamic([200, 302, 401]);
W3CIISLog
| where scStatus has_any (successCodes)
| where ipv4_is_private(cIP) == False
| where csUriStem hasprefix "/autodiscover/autodiscover.json"
| project TimeGenerated, cIP, sIP, sSiteName, csUriStem, csUriQuery, Computer, csUserName, _ResourceId, FileUri
| where (csUriQuery !has "Protocol" and isnotempty(csUriQuery))
or (csUriQuery has_any("/mapi/", "powershell"))
or (csUriQuery contains "@" and csUriQuery matches regex @"\.[a-zA-Z]{2,4}?(?:[a-zA-Z]{2,4}\/)")
or (csUriQuery contains ":" and csUriQuery matches regex @"\:[0-9]{2,4}\/")
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend AccountName = tostring(split(csUserName, "@")[0]), AccountUPNSuffix = tostring(split(csUserName, "@")[1])

let scriptExtensions = dynamic([".php", ".jsp", ".js", ".aspx", ".asmx", ".asax", ".cfm", ".shtml"]);
http_proxy_oab_CL
| where RawData contains "Download failed and temporary file"
| extend File = extract("([^\\\\]*)(\\\\[^']*)",2,RawData)
| extend Extension = strcat(".",split(File, ".")[-1])
| extend InteractiveFile = iif(Extension in (scriptExtensions), "Yes", "No")
// Uncomment the following line to alert only on interactive file download type
//| where InteractiveFile =~ "Yes"
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)

let suspiciousCmdLineKeywords = dynamic(["http://", "https://"]);
// Identify exchange servers based on known paths
// Summarize these to get a list of exchange server hostnames
let exchangeServers = W3CIISLog
| where csUriStem has_any("/owa/","/ews/","/ecp/","/autodiscover/")
// Only where successful, rule out failed scanning
| where scStatus startswith "2"
| summarize by Computer;
DeviceProcessEvents
| where DeviceName in~ (exchangeServers)
// Where the IIS worker process initiated CMD or PowerShell
| where InitiatingProcessParentFileName == "w3wp.exe"
| where InitiatingProcessFileName has_any("cmd.exe", "powershell.exe")
// Where CMD or PowerShell command line included parameters associated with CVE-2022-41040/CVE-2022-41082 exploitation
| where ProcessCommandLine has_any(suspiciousCmdLineKeywords)
| project TimeGenerated, DeviceId, DeviceName, InitiatingProcessFileName, ProcessCommandLine, AccountDomain = InitiatingProcessAccountDomain, AccountName = InitiatingProcessAccountName
| extend Account = strcat(AccountDomain, "\\", AccountName)
| extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(DeviceName, DomainIndex + 1), DeviceName)