Query Details
Sentinel Data Connector Health
Query
# Sentinel - Data Connector Health
## Query Information
### Description
Use the below queries to gain insights into data connector health
#### References
- [Monitor the health of your data connectors](https://learn.microsoft.com/en-us/azure/sentinel/monitor-data-connector-health)
### Microsoft Sentinel
Detect latest failure events per connector
```kql
SentinelHealth
| where TimeGenerated > ago(3d)
| where OperationName == 'Data fetch status change'
| where Status in ('Success', 'Failure')
| summarize TimeGenerated = arg_max(TimeGenerated,*) by SentinelResourceName, SentinelResourceId
| where Status == 'Failure'
```
Detect connectors with changes from fail to success state
```kql
let lastestStatus = SentinelHealth
| where TimeGenerated > ago(12h)
| where OperationName == 'Data fetch status change'
| where Status in ('Success', 'Failure')
| project TimeGenerated, SentinelResourceName, SentinelResourceId, LastStatus = Status
| summarize TimeGenerated = arg_max(TimeGenerated,*) by SentinelResourceName, SentinelResourceId;
let nextToLastestStatus = SentinelHealth
| where TimeGenerated > ago(12h)
| where OperationName == 'Data fetch status change'
| where Status in ('Success', 'Failure')
| join kind = leftanti (lastestStatus) on SentinelResourceName, SentinelResourceId, TimeGenerated
| project TimeGenerated, SentinelResourceName, SentinelResourceId, NextToLastStatus = Status
| summarize TimeGenerated = arg_max(TimeGenerated,*) by SentinelResourceName, SentinelResourceId;
lastestStatus
| join kind=inner (nextToLastestStatus) on SentinelResourceName, SentinelResourceId
| where NextToLastStatus == 'Failure' and LastStatus == 'Success'
```
Detect connectors with changes from success to fail state
```kql
let lastestStatus = SentinelHealth
| where TimeGenerated > ago(12h)
| where OperationName == 'Data fetch status change'
| where Status in ('Success', 'Failure')
| project TimeGenerated, SentinelResourceName, SentinelResourceId, LastStatus = Status
| summarize TimeGenerated = arg_max(TimeGenerated,*) by SentinelResourceName, SentinelResourceId;
let nextToLastestStatus = SentinelHealth
| where TimeGenerated > ago(12h)
| where OperationName == 'Data fetch status change'
| where Status in ('Success', 'Failure')
| join kind = leftanti (lastestStatus) on SentinelResourceName, SentinelResourceId, TimeGenerated
| project TimeGenerated, SentinelResourceName, SentinelResourceId, NextToLastStatus = Status
| summarize TimeGenerated = arg_max(TimeGenerated,*) by SentinelResourceName, SentinelResourceId;
lastestStatus
| join kind=inner (nextToLastestStatus) on SentinelResourceName, SentinelResourceId
| where NextToLastStatus == 'Success' and LastStatus == 'Failure'
```
```kql
```
```kql
```
```kql
```
Explanation
The query is used to monitor the health of data connectors in Microsoft Sentinel.
The first query detects the latest failure events per connector by filtering for events where the operation name is "Data fetch status change" and the status is either "Success" or "Failure". It then summarizes the results by the latest time generated for each connector and filters for events where the status is "Failure".
The second query detects connectors that have changed from a failed state to a successful state. It first retrieves the latest status for each connector within the last 12 hours and stores it in the "lastestStatus" variable. It then retrieves the next-to-latest status for each connector within the last 12 hours and stores it in the "nextToLastestStatus" variable. The two variables are then joined based on the connector name and ID, and filtered for events where the next-to-latest status is "Failure" and the latest status is "Success".
The third query is similar to the second query, but it detects connectors that have changed from a successful state to a failed state. It follows the same steps as the second query, but filters for events where the next-to-latest status is "Success" and the latest status is "Failure".
Details
Alex Verboon
Released: September 18, 2023
Tables
Keywords
Operators