Microsoft security threat protection reports - need kql please

Frequent Contributor

Hi everyone,


In, there are report for 'threat protection' related to MDE.


eg: "detection source of all alerts by creation date"


Is there anywhere that I can find the kql for these charts?


I'd like to replicate these charts in the Sentinel workbooks so I don't have to look at the in the security portal.


Thank you.


5 Replies
best response confirmed by bobsyouruncle (Frequent Contributor)



The [Microsoft 365 Defender (Preview)] connector only takes over Device* tables (and these are optional only if you need that data in Azure Sentinel) or put Alerts into the SecurityAlert table. You may not have enough data to re-create the precise alert even if you had the KQL

So you can use, KQL like:

| where ProductName in("Microsoft Defender Advanced Threat Protection", "Office 365 Advanced Threat Protection", "Azure Advanced Threat Protection", "Microsoft Cloud App Security", "Microsoft 365 Defender")
| summarize count(AlertName) by ProductName

or (very basic KQL to read any Device* Table)

union Device* | summarize count() by DeviceName, Type


Thanks Clive, that's pretty much what we did - just took a couple of hours of playing around with it.

I'm not finding the SecurityAlert table under Monitor > Logs, is that table only available for Sentinel?  I thought you could use it to find ASC alerts?







The SecurityAlert table is just in Sentinel and will show you any alerts for connected MS security products - like Defender ATP, Azure Security Center, Cloud App Security, Identity Protection, Defender for ID and Sentinel alerts themselves.

| summarize count()by AlertName, ProviderName

So in the SecurityAlert table you will get alerts from MDE, and you also have the ability to send over any of the Device* tables, however as Clive mentioned that may not be enough to replicate that report. Having a look through my tenant it doesn't appear that the 'Detection source' is sent as part of the alert to the SecurityAlert table.
Under Monitor > Logs you have the Alerts table, but as above the Security Alerts are in SecurityAlerts if you have ASC or Azure Sentinel.