Rod’s Blog

Share this post

Rod’s Blog
Rod’s Blog
How to be Notified When Microsoft Sentinel Data Stops Populating
Copy link
Facebook
Email
Notes
More

How to be Notified When Microsoft Sentinel Data Stops Populating

Unpopulated

Rod Trent
Oct 02, 2023
4

Share this post

Rod’s Blog
Rod’s Blog
How to be Notified When Microsoft Sentinel Data Stops Populating
Copy link
Facebook
Email
Notes
More
Share

The idea behind this solution is to be alerted when data ingestion has stopped for a specific table or originating service, i.e., ingestion health. As a security analyst, having the most current data is critically important – which makes knowing when data has stopped flowing also an important factor. But a lot of times it’s also not the analyst’s job to dig into Data Connector or data ingestion problems. In a lot of cases, that responsibility falls on the IT, infrastructure, or operations team. The analyst (Investigative or Hunting) should be able to focus solely on identifying threats, not the tool being used. The following solution runs as an Analytics Rule, but then can be setup to simply send an email to the individual or team responsible for maintaining integrity of the security tool.

The Solution

Here’s a KQL query designed to be used as an Analytics rule. You can replace ‘HuntingBookmark’ with a table name you are most interested in being alerted to. I use the HuntingBookmark table here to test with because I know I’ve not created a bookmark in this specific tenant in a while. (For a query that can be used to monitor multiple tables at once, see further below)

Rod’s Blog is a reader-supported publication. To receive new posts and support my work, consider becoming a free or paid subscriber.

The query looks through data from the past 30 days and determines if the table has not received any new data in the past 3 days. The calculation for last_log is based on seconds.

In the current query, 259,200 = 3 days. i.e., 60 seconds x 60 minutes x 24 hours x 3 days = 259,200

//Replace the table name with the name you want to track. Create an Analytics Rule and be notified if a table has not received new data in the last 3 days.
//Seconds calculation for last_log is 60 x 60 x 24 x 3 = 259200
//Make sure to set the Lookback to 14 days

HuntingBookmark
| where TimeGenerated > ago(30d)
| summarize last_log = datetime_diff("second",now(), max(TimeGenerated))
| where last_log >= 259200

The most current version of this query will always be at: https://github.com/rod-trent/SentinelKQL/blob/master/DataIngestionNotHappening.txt

I have this configured and set to run based on the following schedule and parameters. Take note that I have configured Suppression (once an alert is generated it will stop running the query to give me time to address it) and Disabled creation of an Incident. Microsoft Sentinel is a security tool and I’m always very wary about generating Incidents that are not security focused. So, instead of creating an Incident for this, I’m just integrating a Playbook that simply sends an email with the message shown down below.

Analytics Rule Schedule, Threshold, and Suppression

Additionally important, as shown above, the Lookup needs to be set to the maximum for the Analytics Rule to detect data. 14 days is the max Lookup you can configure. Make sure to set this.

No Security Incident generated
Just sending an email to alert the ops team about the potential outage
Simple Playbook, just sends an email alert
The email I receive when the criteria is met

If you want a KQL query that looks at multiple tables, here you go…

//Reporting on Multiple tables that are not ingesting data.
//Replace the tables with the tables you want to monitor.
//Want to add more tables? Just copy, paste, and modify the let block to your heart's content.

let table1= OfficeActivity
| where isnotempty(Type)
| summarize maxTimeGenerated=max(TimeGenerated) by Type
| where maxTimeGenerated  < ago(48h);
let table2= SecurityAlert
| where isnotempty(Type)
| summarize maxTimeGenerated=max(TimeGenerated) by Type
| where maxTimeGenerated  < ago(72h);
let table3= DeviceInfo
| where isnotempty(Type)
| summarize maxTimeGenerated=max(TimeGenerated) by Type
| where maxTimeGenerated  < ago(72h);
let table4= SigninLogs
| where isnotempty(Type)
| summarize maxTimeGenerated=max(TimeGenerated) by Type
| where maxTimeGenerated  < ago(14d);
union isfuzzy=true 
table1,table2,table3,table4

The most current version of this query will always be at: https://github.com/rod-trent/SentinelKQL/blob/master/MultipleTablesNoIngest.kql


[Want to discuss this further? Hit me up on Twitter or LinkedIn]

[Subscribe to the RSS feed for this blog]

[Subscribe to the Weekly Microsoft Sentinel Newsletter]

[Subscribe to the Weekly Microsoft Defender Newsletter]

[Subscribe to the Weekly Azure OpenAI Newsletter]

[Learn KQL with the Must Learn KQL series and book]

[Learn AI Security with the Must Learn AI Security series and book]

Rod’s Blog is a reader-supported publication. To receive new posts and support my work, consider becoming a free or paid subscriber.

4

Share this post

Rod’s Blog
Rod’s Blog
How to be Notified When Microsoft Sentinel Data Stops Populating
Copy link
Facebook
Email
Notes
More
Share

Discussion about this post

No posts

Ready for more?

© 2025 Rod Trent
Privacy ∙ Terms ∙ Collection notice
Start WritingGet the app
Substack is the home for great culture

Share

Copy link
Facebook
Email
Notes
More