If you’ve been following my series of blogs about how to use the new Microsoft Sentinel data lake, I hope you’re finding it useful, able to manage your budget better, and see some great results.
All of the insights in this series are extracts from the more detailed series of blogs focused on the Defender portal, called A little slice of…, which I’ve been working on with Jon Shectman, Principal Security Researcher – Lead Investigator at Microsoft.
As well as all the tips and advice I’ve already posted, you might like to read mine and Jon’s list of recommended daily and weekly queries for your Security Operations Centre (SOC) to run maximise efficiency.
Daily queries
Query 1: Data Connector Health
Gain visibility into any data connectors that have disconnected and stopped populating tables. This builds a lightweight, continuously up to date “data connector health board” for connectors.
Query 2: Table Ingestion Change from Yesterday until Today
Pinpoint which tables had a significant ingestion drop in the last day.
Query 3: Daily Incident Check by Count
Produce a ranked list of incident titles showing how many times each title appears and the most recent activity (plus creation time) for that title – quickly show repeat incident patterns and when they last occurred.
Query 4: Daily Incident Count for Last 90 Days+90-Day Forecast
Create a daily time series of incident counts and then use built-in Kusto forecasting to project incident volume ~90 days into the future.
Query 5: New vs. resolved Incidents, plus Mean Time to Close (MTTC)
Produces yesterday’s daily ops snapshot from the SecurityIncident table.
Query 6: Median Time to Resolve (MTTR) reporting for Last 90 days
Produce a severity‑level service level agreement (SLA) and efficiency report from SecurityIncident.
Query 7: Authentication Requirement Type
Produce a high‑value authentication overview that’s perfect for dashboards, access‑pattern analysis, and Zero Trust reporting.
Query 8: MFA Coverage Rate for Last 90 Days Including Privileged Users
This query measures MFA coverage overall and multi-factor authentication (MFA) coverage for privileged users over the last 90 days.
Rather than repeat my detailed descriptions from the A little slice of… series, you can find out more about each of these eight daily queries in A Little Slice of…Daily Queries for your SOC to Run, Part 1 and A Little Slice of…Daily Queries for your SOC to Run, Part 2.
Weekly queries
Query 1: Data Connector Health & Ingestion Status (Last 7 Days)
Monitor the health of data ingestion for Microsoft Sentinel data tables over the past seven days.
Query 2: Ingestion Drops
Detect statistically significant drops in daily ingestion per data table by comparing today’s 24-hour volume to weekday-aware baselines.
Query 3: Incident Title Trends & Health (7-day vs previous 90-day)
Aggregate security incidents by Title, comparing activity in the last seven days to the previous 90 days and provides a 90-day daily series for charting.
Query 4: Rule Firing Drift – SecurityAlert (14-day window, compares prior vs current week)
Compares alert counts per AlertName across two seven-day windows (Prior = 8–14 days ago, Current = last 7 days).
Query 5: Security Alert Activity by Product Name
Aggregate SecurityAlert activity by ProductName, comparing the last seven days against the previous 90-day window (excluding the last seven days), and assign a simple human-friendly health flag.
Query 6: Top Billable Tables and Health (Baseline) Check
Compare billable ingestion for each data table over the last seven days against the previous seven-day window and flag large increases.
Query 7: Analytics Rule Execution Health
Review SentinelHealth events for Analytics rules over the last seven days and surfaces rules with execution issues (Failures, Partial Success, or Warnings).
You’ll see more information on each of these weekly queries in A Little Slice of…Weekly Queries for your SOC to Run, Part 1 and A Little Slice of…Weekly Queries for your SOC to Run, Part 2.
Currently these queries are in in v1.6.4 of “Workspace Usage” found in the Sentinel Content Hub, this version has been submitted and should show up soon – in the meantime v1.6.3 is available.
If you would like access to v1.6.4, let me know and I can send you a direct link.
I really hope you find these useful. Should you have questions about Sentinel data lake, or want to talk to me or my colleagues about your cyber security needs, then please get in touch.
