KQL Search

Query Details

HomeAI ToolsDevice Query

HUNT 16 M365 Threat Intel IP Correlation 30d

Query

// Hunt    : M365 - ThreatIntelligence IP Correlation with M365 Activity (30d)
// Purpose : Proactively join all M365 OfficeActivity IPs against active Sentinel
//           ThreatIntelIndicators entries over 30 days. Surfaces any historical
//           access to mailboxes, SharePoint, OneDrive, or Teams from IPs associated
//           with known threat actors, C2 infrastructure, or malware campaigns.
//           Use this after a new TI feed ingestion to retroactively hunt for past
//           exposure — "did this known-bad IP ever touch our tenant?"
//           Note: uses ThreatIntelIndicators (STIX 2.1, replaces deprecated
//           ThreatIntelligenceIndicator table).
//
// Tables  : OfficeActivity, ThreatIntelIndicators
// Period  : P30D
//==========================================================================================

let LookbackDays = 30d;

// Materialize active TI indicators (STIX 2.1 pattern extraction)
let TIIndicators = ThreatIntelIndicators
    | where TimeGenerated > ago(90d)
    | where IsActive == true
    | where isempty(ValidUntil) or ValidUntil > now()
    | where Pattern has "ipv4-addr:value"
    | extend TIIp = extract(@"ipv4-addr:value\s*=\s*'([^']+)'", 1, tostring(Pattern))
    | where isnotempty(TIIp)
    | summarize
        ThreatType        = make_set(Tags, 5),
        ConfidenceScore   = max(Confidence),
        Description       = any(tostring(Data.description)),
        IndicatorProvider = any(SourceSystem),
        FirstTISeen       = min(TimeGenerated),
        LastTISeen        = max(TimeGenerated)
        by TIIp;

// Join all M365 events
OfficeActivity
| where TimeGenerated > ago(LookbackDays)
| where isnotempty(ClientIP)
| join kind=inner TIIndicators on $left.ClientIP == $right.TIIp
| extend
    IsHighRiskOp = Operation in (
        "FileDownloaded", "FileSyncDownloadedFull",
        "MailItemsAccessed", "MessageViewed",
        "New-InboxRule", "New-TransportRule",
        "SharingInvitationCreated", "AnonymousLinkCreated",
        "AnonymousLinkUsed", "Add-MailboxPermission",
        "New-MailboxExportRequest", "MemberAdded",
        "MessageCreated", "FileShared")
| summarize
    TotalHits         = count(),
    HighRiskHits      = countif(IsHighRiskOp),
    Operations        = make_set(Operation, 20),
    AffectedUsers     = make_set(UserId, 10),
    Workloads         = make_set(OfficeWorkload, 5),
    FilesOrObjects    = make_set(coalesce(SourceFileName, OfficeObjectId), 10),
    ThreatType        = any(ThreatType),
    ConfidenceScore   = any(ConfidenceScore),
    TIDescription     = any(Description),
    IndicatorProvider = any(IndicatorProvider),
    FirstSeen         = min(TimeGenerated),
    LastSeen          = max(TimeGenerated)
    by MaliciousIP = ClientIP
| sort by HighRiskHits desc, TotalHits desc
| extend RiskClassification = case(
    HighRiskHits >= 5,   "Critical — Active Attacker Operations",
    HighRiskHits >= 1,   "High — Sensitive Actions from TI IP",
    TotalHits >= 10,     "Medium — High-Volume TI IP Access",
    "Low — Single/Recon Access")
| project
    MaliciousIP,
    ThreatType,
    ConfidenceScore,
    TIDescription,
    IndicatorProvider,
    TotalHits,
    HighRiskHits,
    AffectedUsers,
    Operations,
    Workloads,
    FilesOrObjects,
    RiskClassification,
    FirstSeen,
    LastSeen

Explanation

This query is designed to identify any historical access to Microsoft 365 services (like mailboxes, SharePoint, OneDrive, or Teams) from IP addresses associated with known threats, such as malware campaigns or command-and-control infrastructure. It does this by comparing IP addresses from recent Microsoft 365 activity logs with a list of known malicious IPs from threat intelligence data.

Here's a simplified breakdown of what the query does:

  1. Define a Time Frame: It looks back over the past 30 days of Microsoft 365 activity.

  2. Extract Threat Intelligence Data: It gathers active threat intelligence indicators from the last 90 days, focusing on those related to IP addresses.

  3. Join Data: It matches the IP addresses from Microsoft 365 activity logs with those in the threat intelligence data.

  4. Identify High-Risk Operations: It flags certain operations as high-risk, such as downloading files, accessing mail items, or creating new rules in mailboxes.

  5. Summarize Findings: It counts the total number of matches and high-risk operations, lists affected users and operations, and provides details about the threat intelligence indicators.

  6. Classify Risk: It classifies the risk level of each IP based on the number and type of operations detected, ranging from "Critical" to "Low."

  7. Output Results: It presents a summary of the findings, including the malicious IP, threat type, confidence score, description, provider, number of hits, affected users, operations, and risk classification.

This query is useful for security teams to retroactively identify potential security breaches or suspicious activities within their Microsoft 365 environment.

Details

David Alonso profile picture

David Alonso

Released: May 14, 2026

Tables

OfficeActivityThreatIntelIndicators

Keywords

ThreatIntelligenceIPMailboxesSharePointOneDriveTeamsThreatActorsInfrastructureMalwareCampaignsIndicatorsEventsOperationsUsersWorkloadsFilesObjects

Operators

let|where>ago()==isempty()ornow()hasextendextract()tostring()isnotempty()summarizemake_set()max()any()min()joinkind=inneronincount()countif()coalesce()sort bydesccase()>=project

Actions