KQL Search

Query Details

HomeAI ToolsDevice Query

Defender XDR Alert Evidence Summarized

Query

AlertEvidence
| where Timestamp > ago(1d)
| where AlertId == "<<<>>>"
| extend AdditionalFields = bag_remove_keys(todynamic(AdditionalFields), dynamic(["MergeByKey", "MergeByKeyHex"]))
| extend AdditionalFieldsType = iff(EntityType == "GenericEntity", tostring(AdditionalFields["Type"]), "")
| extend AllInfo = bag_remove_keys(pack_all(true), dynamic(["Timestamp", "Title", "Severity", "Categories", "AttackTechniques", "ServiceSource", "DetectionSource", "AlertId","EntityType"]))
| extend Packed = bag_remove_keys(AllInfo, dynamic(["EvidenceRole", "EvidenceDirection", "AdditionalFields", "AdditionalFieldsType"]))
| extend PackedKeys = bag_keys(Packed)
| summarize
    AllInfo = make_list(AllInfo),
    EntityList = make_list_if(Packed, array_length(PackedKeys) > 0),
    EntityKeys = make_set_if(PackedKeys, array_length(PackedKeys) > 0),
    take_any(Timestamp, Title, Severity, Categories, AttackTechniques, ServiceSource, DetectionSource)
    by AlertId, EntityType
| summarize
    AllInfo = make_bag(bag_pack(EntityType, AllInfo)),
    BagToUnpack = make_bag_if(bag_pack(EntityType, EntityList), array_length(EntityKeys) > 0),
    ColumnKeys = make_bag_if(bag_pack(EntityType, EntityKeys), array_length(EntityKeys) > 0),
    take_any(Timestamp, Title, Severity, Categories, AttackTechniques, ServiceSource, DetectionSource)
    by AlertId
| extend EntityColumns = bag_keys(BagToUnpack)
| evaluate bag_unpack(BagToUnpack)
| project-reorder Timestamp, AlertId, ServiceSource, DetectionSource, Title, Severity, Categories, AttackTechniques, EntityColumns, ColumnKeys

Explanation

This KQL (Kusto Query Language) query is designed to process and summarize alert evidence data. Here's a simplified breakdown of what it does:

  1. Filter Data: It starts by filtering the AlertEvidence table to include only records from the last day (Timestamp > ago(1d)) and with a specific AlertId.

  2. Process Additional Fields: It removes certain keys (MergeByKey, MergeByKeyHex) from the AdditionalFields and extracts the Type if the EntityType is "GenericEntity".

  3. Pack and Remove Keys: It creates a packed version of all information, excluding some standard fields like Timestamp, Title, etc., and further removes specific keys related to evidence roles and directions.

  4. Summarize Information: It aggregates the data by AlertId and EntityType, creating lists of all information and entities, and sets of entity keys.

  5. Further Summarization: It aggregates the data again by AlertId, creating bags (dictionaries) of information and keys for each entity type.

  6. Unpack and Reorder: It unpacks the bags of entity information and reorders the columns for the final output.

The result is a structured summary of alert evidence, organized by alert ID, with key information and entity details neatly packed and unpacked for easy analysis.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: October 16, 2024

Tables

AlertEvidence

Keywords

AlertEvidenceEntityTypeTimestampTitleSeverityCategoriesAttackTechniquesServiceSourceDetectionSourceEntityColumnsColumnKeys

Operators

whereago==extendbag_remove_keystodynamicdynamicifftostringpack_allbag_keyssummarizemake_listmake_list_ifarray_lengthmake_set_iftake_anybymake_bagbag_packmake_bag_ifevaluatebag_unpackproject-reorder

Actions