kqlo365security-compliance

Including multiple values in a column in KQL


I am trying to figure out a way to return multiple values in a single column in KQL in Microsoft 365 Defender. For example, if I am trying to output multiple file names from the EmailAttachmentInfo schema, how would I go about doing so?

EmailAttachmentInfo
| where FileName matches regex "Interesting_File_\d+\.zip"
| project FileName

Thank you!


Solution

  • // Data sample generation. Not part of the solution
    let EmailAttachmentInfo = 
    datatable(customer_id:int, FileName:string)
    [
        1 ,'file1.zip'
       ,1 ,'file2.zip'
       ,1 ,'file1.zip'
       ,1 ,'file3.zip'
       ,1 ,'file2.zip'
       ,2 ,'file3.zip'
       ,2 ,'file3.zip'
       ,2 ,'file4.zip'
    ];
    // Solution starts here
    EmailAttachmentInfo
    | summarize make_set(FileName), make_list(FileName) by customer_id
    
    customer_id set_FileName list_FileName
    1 ["file1.zip","file2.zip","file3.zip"] ["file1.zip","file2.zip","file1.zip","file3.zip","file2.zip"]
    2 ["file3.zip","file4.zip"] ["file3.zip","file3.zip","file4.zip"]

    Fiddle