SOLVED

Merge identical values from different variables

%3CLINGO-SUB%20id%3D%22lingo-sub-2833219%22%20slang%3D%22en-US%22%3EMerge%20identical%20values%20from%20different%20variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2833219%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20recently%20been%20trying%20to%20figure%20out%20a%20decent%20way%20to%20make%20an%20alert%20when%20a%20certain%20amount%20of%20informational%20alerts%20triggers%20from%20other%20Defender%20products%2C%20like%20for%20example%20large%20amounts%20of%20Emails%20with%20malicious%20URL's%20removed.%20This%20could%20indicate%20a%20phishing%20campaign%20that%20i%20would%20like%20to%20be%20notified%20about.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22stianhoydal_0-1633942416986.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316461i0F5E0CA622D8933B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22stianhoydal_0-1633942416986.png%22%20alt%3D%22stianhoydal_0-1633942416986.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20sender%20domains%20are%20stored%20in%20different%20parts%20of%20Entities%20although%20they%20are%20from%20the%20same%20sender.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20merge%20these%20into%20one%20variable%20instead%20of%20having%20them%20separated%20like%20this.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2833311%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20identical%20values%20from%20different%20variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2833311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F819982%22%20target%3D%22_blank%22%3E%40stianhoydal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Emv_expand%20will%20help%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%0ASecurityAlert%0A%7C%20where%20Description%20contains%20%22Emails%20with%20malicious%20URL%22%0A%7C%20mv-expand%20todynamic(Entities)%0A%2F%2F%7C%20summarize%20make_set(Entities.P2SenderDomain)%0A%7C%20where%20isnotempty(Entities.P2SenderDomain)%0A%7C%20distinct%20tostring(Entities.P2SenderDomain)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityAlert%0A%7C%20where%20Description%20contains%20%22Emails%20with%20malicious%20URL%22%0A%7C%20mv-expand%20todynamic(Entities)%0A%7C%20summarize%20make_set(Entities.P2SenderDomain)%20by%20AlertName%2C%20tostring(Entities.MailboxPrimaryAddress)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3Elots%20of%20other%20examples%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fsearch%3Fq%3Dmv-expand%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESearch%20%C2%B7%20mv-expand%20(github.com)%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Greetings, 

 

I have recently been trying to figure out a decent way to make an alert when a certain amount of informational alerts triggers from other Defender products, like for example large amounts of Emails with malicious URL's removed. This could indicate a phishing campaign that i would like to be notified about. 

 

The problem is this:

stianhoydal_0-1633942416986.png

The sender domains are stored in different parts of Entities although they are from the same sender. 

Is there a way to merge these into one variable instead of having them separated like this. 

2 Replies
best response confirmed by stianhoydal (Contributor)
Solution

@stianhoydal 

 

mv_expand will help

 


SecurityAlert
| where Description contains "Emails with malicious URL"
| mv-expand todynamic(Entities)
//| summarize make_set(Entities.P2SenderDomain)
| where isnotempty(Entities.P2SenderDomain)
| distinct tostring(Entities.P2SenderDomain)

 

or

 

SecurityAlert
| where Description contains "Emails with malicious URL"
| mv-expand todynamic(Entities)
| summarize make_set(Entities.P2SenderDomain) by AlertName, tostring(Entities.MailboxPrimaryAddress)


lots of other examples: Search · mv-expand (github.com)

 

Aha, so if i understand this correct the mv_expand unfolds the previously aggregated Entities into singular entries making it possible to search across them without having to look trough different possible locations within the Entities category?

Thanks for a quick and easy answer!