Correlate Microsoft 365 Telemetry for Better Insights

Core streams (collect at minimum)

• Identity (Entra ID / Azure AD): SigninLogs (+ risk state, location, device, app)

• Unified M365 audit: OfficeActivity (SharePoint/OneDrive, Teams, Exchange, Purview events)

• Usage & licensing: Graph reports (active users, feature usage), assigned SKUs

Goal: triangulation. Every detection/insight should use ≥2 streams to reduce false positives and add business context.

KPIs that matter (and what to correlate)

1. Risky access + data movement

   • Spike in failed or risky sign-ins AND large SharePoint/OneDrive downloads or external shares.

2. Adoption vs. login

   • Users logging into Teams but never using meetings, apps, or channels → training need.

3. License spend vs. activity

   • E5/E3 assigned AND zero meaningful activity over 30–60 days → reclaim or downgrade.

4. Email vs. Teams split-brain

   • Big Exchange threads while Teams for that group is idle → collaboration friction.

5. External collaboration exposure

   • New external Teams chats/meetings AND same recipients appear in SharePoint external shares.

---

Copy-paste KQL (Sentinel or M365 Defender Advanced Hunting)

Use in Microsoft Sentinel (Log Analytics) or Microsoft 365 Defender Advanced Hunting.
Table names differ slightly; notes included.

1) High-risk sign-ins + download spike (past 24h)

// Sentinel: SigninLogs + OfficeActivity let risky_signins = SigninLogs | where TimeGenerated > ago(24h) | where RiskState in ("atRisk", "confirmedCompromised") or RiskLevelAggregated != "none" | summarize riskySignins=count(), firstSeen=min(TimeGenerated), lastSeen=max(TimeGenerated) by UserPrincipalName, IPAddress, AppDisplayName; let big_downloads = OfficeActivity | where TimeGenerated > ago(24h) | where Workload in ("SharePoint", "OneDrive") and Operation in ("FileDownloaded", "FileSyncDownloadedFull", "FilePreviewed") | summarize downloads=count(), uniqueSites=dcount(SiteUrl) by UserId, UserKey, UserType=tolower(UserType); risky_signins | join kind=innerunique ( big_downloads | project UserId, downloads, uniqueSites ) on $left.UserPrincipalName == $right.UserId | where downloads >= 50 or uniqueSites >= 3 | project UserPrincipalName, IPAddress, AppDisplayName, riskySignins, downloads, uniqueSites, firstSeen, lastSeen | order by downloads desc

M365 Defender AH: use IdentityLogonEvents (risk via RiskState if available) + OfficeActivity.

2) External sharing burst following failed sign-ins

let failed = SigninLogs | where TimeGenerated > ago(24h) | where ResultType !in ("0", "50125", "50140") // non-success (tune if needed) | summarize fails=count() by UserPrincipalName, bin(TimeGenerated, 30m); let shares = OfficeActivity | where TimeGenerated > ago(24h) | where Workload in ("SharePoint", "OneDrive") and Operation in ("SharingSet", "AnonymousLinkCreated", "SecureLinkCreated") | where isnotempty(SharingType) or isnotempty(ExternalUserName) | summarize externalShares=count(), lastShare=max(TimeGenerated) by UserId, bin(TimeGenerated, 30m); failed | join kind=inner (shares) on $left.UserPrincipalName == $right.UserId and $left.TimeGenerated == $right.TimeGenerated | where externalShares >= 5 | project UserPrincipalName, fails, externalShares, lastShare

3) Teams activity gap (logins but no features)

// "Logged in" but no Meetings/Channel activity in 14d let logins = SigninLogs | where TimeGenerated > ago(14d) and AppDisplayName has "Teams" | summarize logins=count() by UserPrincipalName; let teams_use = OfficeActivity | where TimeGenerated > ago(14d) | where Workload == "MicrosoftTeams" | where Operation in ("MeetingStarted","CallStarted","ChannelMessageSent","ChatMessageSent","AppInstalled") | summarize actions=count() by UserId; logins | join kind=leftouter teams_use on $left.UserPrincipalName == $right.UserId | extend actions = coalesce(actions, 0) | where logins >= 3 and actions == 0 | project UserPrincipalName, logins, actions

4) License assigned but no meaningful activity (60d)

Feed a reference of assigned SKUs into a Sentinel watchlist or Defender AH custom table.

let licensed = externaldata(UserPrincipalName:string, Sku:string) [@"https://<yourstorage>/licensed.csv"] with (format="csv", has_header_row=true); let activity = OfficeActivity | where TimeGenerated > ago(60d) | where Workload in ("SharePoint","OneDrive","MicrosoftTeams","Exchange") | summarize acts=count(), lastSeen=max(TimeGenerated) by UserId; licensed | join kind=leftouter (activity) on $left.UserPrincipalName == $right.UserId | extend acts = coalesce(acts, 0) | where acts == 0 | project UserPrincipalName, Sku, lastSeen

5) Email vs Teams “split-brain” signal (stalled collaboration)

let email_threads = EmailEvents // Defender AH | where Timestamp > ago(7d) | summarize emailCount=count() by SenderFromAddress, bin(Timestamp, 1d); let teams_msgs = OfficeActivity | where TimeGenerated > ago(7d) and Workload == "MicrosoftTeams" and Operation in ("ChannelMessageSent","ChatMessageSent") | summarize teamsCount=count() by UserId, bin(TimeGenerated, 1d); email_threads | join kind=leftouter (teams_msgs) on $left.SenderFromAddress == $right.UserId and $left.Timestamp == $right.TimeGenerated | summarize totalEmail=sum(emailCount), totalTeams=sum(teamsCount) by SenderFromAddress | where totalEmail > 100 and totalTeams < 5

---

PowerShell quick grabs (for data mart / Power BI)

Unified Audit Log (UAL) export (rolling)

# Exchange Online PowerShell Connect-ExchangeOnline $start = (Get-Date).AddDays(-7) $end = Get-Date Search-UnifiedAuditLog -StartDate $start -EndDate $end -ResultSize 5000 | Select-Object CreationDate,UserIds,Operations,AuditData | Export-Csv .\UAL_7d.csv -NoTypeInformation

Entra sign-ins (Graph) for Power BI

# Requires MSGraph PowerShell & AuditLog.Read.All Connect-MgGraph -Scopes "AuditLog.Read.All","Directory.Read.All" Get-MgAuditLogSignIn -All -Filter "createdDateTime ge $(Get-Date).AddDays(-7).ToString('o')" | Select-Object CreatedDateTime,UserDisplayName,UserPrincipalName,AppDisplayName,IPAddress,Status | Export-Csv .\Signins_7d.csv -NoTypeInformation

License assignment snapshot (Graph)

Connect-MgGraph -Scopes "User.Read.All" Get-MgUser -All -Property UserPrincipalName,AssignedLicenses | Select-Object UserPrincipalName, @{n='Skus';e={$_.AssignedLicenses.SkuId -join ';'}} | Export-Csv .\Licenses.csv -NoTypeInformation

---

Power BI: Minimal data model (works)

Tables

• Signins (UserPrincipalName, App, Risk, IP, Time)

• OfficeActivity (Time, Workload, Operation, UserId, SiteUrl, ItemType, Target)

• Licenses (UserPrincipalName, SKU, Dept/Manager if available)

• (Optional) Users (Dept, Country, Manager) for slicing

Relationships

• Users[UPN] ↔ Signins[UserPrincipalName]

• Users[UPN] ↔ OfficeActivity[UserId]

• Users[UPN] ↔ Licenses[UserPrincipalName]

Measures

• Risky Sign-ins = COUNTROWS(FILTER(Signins, Signins[Risk] <> "none"))

• SP/OD Downloads = COUNTROWS(FILTER(OfficeActivity, OfficeActivity[Workload] IN {"SharePoint","OneDrive"} && OfficeActivity[Operation] IN {"FileDownloaded","FileSyncDownloadedFull"}))

• Teams Feature Use = COUNTROWS(FILTER(OfficeActivity, OfficeActivity[Workload]="MicrosoftTeams" && OfficeActivity[Operation] IN {"MeetingStarted","ChannelMessageSent","AppInstalled"}))

• Inactive Licensed Users = DISTINCTCOUNTX(FILTER(Licenses, CALCULATE(COUNTROWS(OfficeActivity), ALLEXCEPT(OfficeActivity, OfficeActivity[UserId]))=0), Licenses[UserPrincipalName])

Visuals that matter

• Correlation strip: Risky Sign-ins vs Downloads (scatter, color by Dept)

• Adoption quadrant: Teams logins (x) vs Feature use (y)

• License waste: Bar of SKUs with “no activity 60d”

• External exposure: Card for external shares (last 24/72h) + drill by site/owner

---

Automations (Power Automate / Sentinel Analytics)

Power Automate (from Power BI alert or schedule)

• License reclaim: If InactiveLicensedUsers > threshold or user has 60d inactivity → create ticket to owner/manager with roster → auto-remove after approval.

• Targeted training: Users with Teams logins ≥ 3 and Feature use = 0 → send adaptive card with 15-min micro-training; re-check in 14d.

• Security “double signal”: When Sentinel analytic fires for risky sign-ins and SharePoint external shares, open a high-severity incident + auto-scope DLP review for affected sites.

Sentinel analytic (use KQL #1 above)

• Schedule every 15 minutes; alert suppression 1h per user; severity High; entity mapping → Account, IP, URL/Site.

---

Guardrails & hygiene

• Retention: Ensure Audit is enabled and retained long enough (E5 can extend; standard is 90 days).

• Baselining: Use 30–90 day moving averages; alert on deviations, not raw thresholds.

• Privacy: Treat UPNs/IPs as sensitive; restrict workspace access; mask in broad exec views.

• Change mgmt: Re-validate joins after schema updates (AuditData payloads occasionally shift).

• Human in loop: Automations propose; owners approve for license clawback/security actions.

---

One-page Starter Checklist

• Turn on Unified Audit Log & verify events land in OfficeActivity.

• Stream SigninLogs and OfficeActivity to Sentinel or export to your BI lake.

• Import Licenses and (optionally) Users for slicing.

• Deploy KQL detections #1–#3 and the license inactivity view.

• Build the correlation visuals (Risk vs Downloads, Adoption quadrant).

• Wire Power Automate for license reclaim + training nudges.

• Review weekly: top incidents, savings, training conversion.

---

Closing thought

You don’t need “more dashboards.” You need relationships between identity, activity, and usage—plus small automations that force follow-through. Start with the three correlations above; you’ll uncover waste, risks, and adoption gaps most tenants never see.