How do i join all events related to a single identifier in KQL?

Question

A single alert in 365Defender often contains several events categorized by EntityType. I'm trying to collect all data related to a unique AlertID into a single line so it can all be correlated towards other tables (Device*Events). I've tried doing this through bag_pack > summarize make_set ()by AlertID, but I end up with the data double packed which means it's difficult to work with and if I do unpack it I've lost the ability to work with a single line.

Is there a proper way to do aggregate the data by a unique key in a single line, or am i SOL?

AlertEvidence
| where DetectionSource contains "Office"
| where AttackTechniques contains "phish"
//Collects data
| extend _mail=bag_pack("Title",Title ,"Timestamp",Timestamp, "AccountName",AccountName, "DeviceName",DeviceName, "EmailSubject",EmailSubject, "NetworkMessageId",NetworkMessageId,"FileName",FileName, "FolderPath",FolderPath, "SHA1",SHA1, "RemoteUrl",RemoteUrl)
//Sorts data per AlertID
| summarize make_set(_mail) by AlertId

Answer 1

How to join all events related to a single identifier in KQL:

To achieve your requirement, use Make_list instead of using make_set function to create a list of records for each AlertID.

Your code looks like below after modifications.

AlertEvidence 
| where DetectionSource contains  "Office" 
| where AttackTechniques contains  "phish"  
| extend _mail=pack("Title",Title ,"Timestamp",Timestamp, "AccountName",AccountName, "DeviceName",DeviceName, "EmailSubject",EmailSubject, "NetworkMessageId",NetworkMessageId,"FileName",FileName, "FolderPath",FolderPath, "SHA1",SHA1, "RemoteUrl",RemoteUrl) 
| summarize make_list(_mail) by AlertId

I ran a sample query log using the make_list function to put it in an understandable way, and it worked as explained.

Output:

Refer article by @Arcane Code for more information regarding make_list & make_set.