How to be Notified When Microsoft Sentinel Data Stops Populating
Unpopulated
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)
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 >= 259200The 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.
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.
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,table4The 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]