KQL Search

Query Details

HomeAI ToolsDevice Query

Email MDO User List For Remediation Action

Query

name : List MDO remediation actions via Explorer 
description : 
- Track each cases with Network Message ID
- Sort the users who got a number of actions 
- e.g. Soft Delete, Hard Delete, Move to junk folder, Move to deleted items 
table : 
- EmailEvents
- https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-emailevents-table?view=o365-worldwide
query : |
    EmailEvents
    | where Timestamp > ago(30d)
    | where LatestDeliveryAction in ("Hard delete", "Soft delete", "Moved to junk folder", "Moved to deleted items")
    | summarize HardDelete_NetworkID = make_list_if(strcat(NetworkMessageId, @"\", Timestamp,@"\", Subject), LatestDeliveryAction == "Hard delete"),  
                SoftDelete_NetworkID = make_list_if(strcat(NetworkMessageId, @"\", Timestamp,@"\", Subject), LatestDeliveryAction == "Soft delete"),
                MoveToJunk_NetworkID = make_list_if(strcat(NetworkMessageId, @"\", Timestamp,@"\", Subject), LatestDeliveryAction == "Moved to junk folder"),
                MoveToDelete_NetworkID = make_list_if(strcat(NetworkMessageId, @"\", Timestamp,@"\", Subject), LatestDeliveryAction == "Moved to deleted items") by RecipientEmailAddress
    | extend HardDelete_case = array_length(HardDelete_NetworkID)
    | extend SoftDelete_case = array_length(SoftDelete_NetworkID)
    | extend MoveToJunk_case = array_length(MoveToJunk_NetworkID)
    | extend MoveToDelete_case = array_length(MoveToDelete_NetworkID)
    | extend Sum_case = HardDelete_case + SoftDelete_case + MoveToJunk_case + MoveToDelete_case
    | project RecipientEmailAddress, Sum_case, HardDelete_case, SoftDelete_case, MoveToJunk_case, MoveToDelete_case, HardDelete_NetworkID, SoftDelete_NetworkID, MoveToJunk_NetworkID, MoveToDelete_NetworkID
    | order by Sum_case desc

Explanation

This query retrieves a list of MDO remediation actions via Explorer. It tracks each case with a Network Message ID and sorts the users based on the number of actions they have. The actions include Soft Delete, Hard Delete, Move to junk folder, and Move to deleted items. The query uses the EmailEvents table and filters the data for the past 30 days. It then summarizes the data by creating lists of Network Message IDs for each action type and grouping them by recipient email address. The query also calculates the number of cases for each action type and the total number of cases. Finally, the results are ordered by the total number of cases in descending order.

Details

Kijo Girardi profile picture

Kijo Girardi

Released: August 4, 2023

Tables

EmailEvents

Keywords

EmailEvents,Timestamp,LatestDeliveryAction,NetworkMessageId,Subject,RecipientEmailAddress,Harddelete,Softdelete,Movedtojunkfolder,Movedtodeleteditems,HardDelete_NetworkID,SoftDelete_NetworkID,MoveToJunk_NetworkID,MoveToDelete_NetworkID,HardDelete_case,SoftDelete_case,MoveToJunk_case,MoveToDelete_case,Sum_case

Operators

toscalar()arg_max()count()mv-expandwheresummarizemake_list_ifstrcatextendarray_lengthprojectorder by

Actions