alexverboon

// Use the below query to find the Microsoft Endpoint Configuration Manager - CMPivot initiated queries executed on devices 
// Microsoft Endpoint Configuration Manager 
DeviceEvents
// | where DeviceName == "client01.corp.net"
| where ActionType == "PowerShellCommand"
| where InitiatingProcessCommandLine contains @"C:\windows\CCM\ScriptStore"
| extend pcommand = parse_command_line(InitiatingProcessCommandLine, "windows")
| where pcommand contains "-wmiquery"
| extend pcommand2 = split(pcommand, "-wmiquery")
| mv-expand pcommand2

alexverboon

// AzureAD Conditinoal Access State cmparisson
let CAStateBefore = CloudAppEvents
    | where Timestamp > startofday(ago(30d)) and Timestamp < startofday(ago(1d))
    | where ActionType == "Set-ConditionalAccessPolicy"
    | extend CAId = tostring((split(tostring(parse_json(ActivityObjects)[2].Value), @"\"))[1])
    | extend CAState = extractjson("$.State", tostring((parse_json(ActivityObjects)[3].Value)))
    | extend CAName = tostring(parse_json(ActivityObjects)[6].Value)
    | where isnotempty(CAState)
    | where CAName != "Default Policy"
    | summarize arg_max(Timestamp, *) by CAId

alexverboon

// Enrich DeviceNetworkEvents with the port number Servicename information 
let iana_port_assignments = (externaldata(entry: string ) [@"https://www.iana.org/assignments/service-names-port-numbers/service-names-port-numbers.csv"]
with (format="txt",ignoreFirstRecord=true))
//iana_port_assignments
// Service Name,Port Number,Transport Protocol,Description,Assignee,Contact,Registration Date,Modification Date,Reference,Service Code,Unauthorized Use Reported,Assignment Notes
| extend data = parse_csv(entry)
| extend ServiceName = tostring(data[0])
| extend PortNumber = toint(data[1])
| project ServiceName, PortNumber 
| summarize any(ServiceName) by PortNumber 

alexverboon

// Defender Network Protection - blocked - activity with system account
DeviceEvents
| where ActionType == "ExploitGuardNetworkProtectionBlocked"
| where InitiatingProcessAccountName == "system"
| extend ResponseCat = parse_json(AdditionalFields).ResponseCategory
| extend Uri = parse_json(AdditionalFields).DisplayName
| project Timestamp, DeviceName,DeviceId, RemoteUrl, ResponseCat, InitiatingProcessFileName, InitiatingProcessCommandLine

alexverboon

// Inspiration from https://github.com/jangeisbauer/AdvancedHunting/blob/master/hunt_for_lolbins just changed Processes to Networkevents
// T1218 Living of the land binaries connecting to the internet
// network activities with lolbins
DeviceNetworkEvents
| where RemoteIPType == 'Public'
| where InitiatingProcessFileName contains "Atbroker.exe" or
InitiatingProcessFileName contains "Bash.exe" or
InitiatingProcessFileName contains "Bitsadmin.exe" or
InitiatingProcessFileName contains "Certutil.exe" or
InitiatingProcessFileName contains "Cmdkey.exe" or

alexverboon

Microsoft Defender for Office 365 - Safe Links

Use the below queries to retrieve MDO - Safe Link activities from Microsoft 365 Defender

---

Query

// Retrieve Safe Link activities

alexverboon

let all_windows10_devices = DeviceInfo
| where isnotempty( OSArchitecture)
| extend WinVersion = case(
    OSBuild == "19041","2004",
    OSBuild == "19042","20H2",
    OSBuild == "18363","1909",
    OSBuild == "18362","1903",
    OSBuild == "17763","1809",
    OSBuild == "17134","1803",
    OSBuild == "16299","1709",

alexverboon

// The below query attempts to get the avg Size in MB per client that is send from Microosoft Defender for Endpoint to Azure Sentinel when using the M365 Defender connector
// The calculation is done as following:
// 1. Collect the Usage data for the specified table from the Usage table, for example 'DeviceFileEvents'
// 2. Collect the total # of devices that submitted information into the specified table, for example 'DeviceFileEvents"
// 3 Divide the total BillableDataGB per DataType by the total number of devices that send data to get the avg MB send by client
// 4 finally 'uniion' all tables

let xagotime = 32d;
let xstarttime = 31d;
// File Events

alexverboon

// Windows 10 versions  https://docs.microsoft.com/en-us/windows/release-information/
DeviceInfo
| where isnotempty( OSPlatform) and OSPlatform == "Windows10"
| summarize arg_max(Timestamp,*) by DeviceId
| extend Version = case(
    OSBuild == "19041","2004",
    OSBuild == "19042","20H2",
    OSBuild == "18363","1909",
    OSBuild == "18362","1903",
    OSBuild == "17763","1809",

alexverboon

# run the modified mfa info gathering script stored here
#  https://gist.github.com/alexverboon/f8fd3300dcf999e1a5f5554cad05030d

$mfa = .\MfaAuthMethodsAnalysis.ps1 -TenantId <TENANT ID>
$MFA_Inactive  = @($MFA | Where-Object {$_.MfaAuthMethodCount -eq 0})
$MFA_Active    = @( $MFA | Where-Object {$_.MfaAuthMethodCount -gt 0})
$MFA_Inactive_NoLicense  = @($MFA | Where-Object {$_.MfaAuthMethodCount -eq 0 -and $_.IsLicensed -eq $False})
$MFA_Active_NoLicense    = @($MFA | Where-Object {$_.MfaAuthMethodCount -gt 0 -and $_.IsLicensed -eq $False})
$MFA_InActive_HasLicense = @($MFA | Where-Object {$_.MfaAuthMethodCount -eq 0 -and $_.IsLicensed -eq $true})
$MFA_Active_HasLicense   = @($MFA | Where-Object {$_.MfaAuthMethodCount -gt 0 -and $_.IsLicensed -eq $true})