SOLVED

Microsoft security threat protection reports - need kql please

%3CLINGO-SUB%20id%3D%22lingo-sub-2280622%22%20slang%3D%22en-US%22%3EMicrosoft%20security%20threat%20protection%20reports%20-%20need%20kql%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2280622%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20security.microsoft.com%2C%20there%20are%20report%20for%20'threat%20protection'%20related%20to%20MDE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eeg%3A%26nbsp%3B%22detection%20source%20of%20all%20alerts%20by%20creation%20date%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anywhere%20that%20I%20can%20find%20the%20kql%20for%20these%20charts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20replicate%20these%20charts%20in%20the%20Sentinel%20workbooks%20so%20I%20don't%20have%20to%20look%20at%20the%20in%20the%20security%20portal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282763%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20security%20threat%20protection%20reports%20-%20need%20kql%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282763%22%20slang%3D%22en-US%22%3EThanks%20Clive%2C%20that's%20pretty%20much%20what%20we%20did%20-%20just%20took%20a%20couple%20of%20hours%20of%20playing%20around%20with%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282761%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20security%20threat%20protection%20reports%20-%20need%20kql%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282761%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F434938%22%20target%3D%22_blank%22%3E%40bobsyouruncle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20%5BMicrosoft%20365%20Defender%20(Preview)%5D%20connector%20only%20takes%20over%20Device*%20tables%20(and%20these%20are%20optional%20only%20if%20you%20need%20that%20data%20in%20Azure%20Sentinel)%20or%20put%20Alerts%20into%20the%20%3CSTRONG%3ESecurityAlert%3C%2FSTRONG%3E%20table.%20You%20may%20not%20have%20enough%20data%20to%20re-create%20the%20precise%20alert%20even%20if%20you%20had%20the%20KQL%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20you%20can%20use%2C%20KQL%20like%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityAlert%20%0A%7C%20where%20ProductName%20in(%22Microsoft%20Defender%20Advanced%20Threat%20Protection%22%2C%20%22Office%20365%20Advanced%20Threat%20Protection%22%2C%20%22Azure%20Advanced%20Threat%20Protection%22%2C%20%22Microsoft%20Cloud%20App%20Security%22%2C%20%22Microsoft%20365%20Defender%22)%0A%7C%20summarize%20count(AlertName)%20by%20ProductName%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%20(very%20basic%20KQL%20to%20read%20any%20Device*%20Table)%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Eunion%20Device*%20%7C%20summarize%20count()%20by%20DeviceName%2C%20Type%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

 

2 Replies
best response confirmed by bobsyouruncle (Contributor)
Solution

@bobsyouruncle 

 

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.