Forum Discussion
Microsoft security threat protection reports - need kql please
Hi everyone,
In security.microsoft.com, 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.
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:SecurityAlert | 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 ProductNameor (very basic KQL to read any Device* Table)
union Device* | summarize count() by DeviceName, Type
5 Replies
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:SecurityAlert | 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 ProductNameor (very basic KQL to read any Device* Table)
union Device* | summarize count() by DeviceName, TypeI'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?
Thanks,
Serge
- 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.
SecurityAlert
| 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.
- Thanks Clive, that's pretty much what we did - just took a couple of hours of playing around with it.
