This is early days for something I’ve been working on for a couple customers so expect the solution to change quite a bit. But the concept is solid and sound.
The idea 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.
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.
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
| 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
Currently, 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. Azure 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.
Thoughts? Suggestions? Improvements? Let me know.