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!
// 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"] |