Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
SOLVED

Microsoft security threat protection reports - need kql please

Iron Contributor

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.

 

5 Replies
best response confirmed by SocInABox (Iron Contributor)
Solution

@SocInABox 

 

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 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?

 

Thanks,

 

Serge

 

@CliveWatson 

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.
Under Monitor > Logs you have the Alerts table, but as above the Security Alerts are in SecurityAlerts if you have ASC or Azure Sentinel.
1 best response

Accepted Solutions
best response confirmed by SocInABox (Iron Contributor)
Solution

@SocInABox 

 

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 ProductName

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

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

 

View solution in original post