Forum Discussion
mv-expand - I cannot make it work!!
CodnChips I tend to use it when trying to get the related alerts from an incident. If you look at a row in the SecurityIncident table, you will see the AlertIds field is listed like:
["695ef2b2-ceb1-d087-b3bb-846a8555xxxx","xxxxxxxx-ceb1-d087-b3bb-846a8555xxxx"]which means it is a JSON array and in this case has 2 entries. In order to really use this field you would use mv-expand on the column as in
SecurityIncident | mv-expand AlertIdsThis will create a new row for each entry in the AlertIds column. All the other columns will be the same but the AlertIds column will only contain a single value per row. This makes it much easier to perform a join against the SecurityAlert table to get the alert information.
CodnChips I tend to use it when trying to get the related alerts from an incident. If you look at a row in the SecurityIncident table, you will see the AlertIds field is listed like:
["695ef2b2-ceb1-d087-b3bb-846a8555xxxx","xxxxxxxx-ceb1-d087-b3bb-846a8555xxxx"] |
which means it is a JSON array and in this case has 2 entries. In order to really use this field you would use mv-expand on the column as in
SecurityIncident
| mv-expand AlertIds
This will create a new row for each entry in the AlertIds column. All the other columns will be the same but the AlertIds column will only contain a single value per row. This makes it much easier to perform a join against the SecurityAlert table to get the alert information.
Hi GaryBushey Right - I've got it thankyou.
It's much easier to see the affect on a single alert otherwise there's too much noise to see what happened!!
I've tried that with the AdditionalData field and understand what's happening thankyou.
So, to then expand on your comment RE perform a join, is this is where you're going to pull\refence\merge data from another table?
If you could bolt onto your initial example (if you get time\have the inclination), then it would be greatly apprecaited, thanks
https://github.com/Azure/Azure-Sentinel/search?l=YAML&q=mv-expand+AlertIds
Note: I searched for mv-expand and AlertIds, now you have two real examples.
Extract of part of one of these example queries:SecurityIncident | summarize hint.strategy = shuffle arg_max(LastModifiedTime, *) by IncidentNumber | mv-expand AlertIds | extend AlertId = tostring(AlertIds) | join kind= innerunique ( SecurityAlert ) on $left.AlertId == $right.SystemAlertId | summarize hint.strategy = shuffle arg_max(TimeGenerated, *), NumberOfUpdates = count() by SystemAlertId- That's interesting - on one of the examples, it shows you can do "nested" mv-expands
- now you are past the rabbit in the headlight stage I see 😉
- Clive_Watson Thanks Clive - I've got to start smaller! I'm like rabbit in the headlights with those examples!! 😄
