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, thankshttps://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
You can get some code examples from the Sentinel Github, via the search box, then filter on YAML files (to reduce some noise, the json files can also be useful...but start simple!).
You should end up with something like:
https://github.com/Azure/Azure-Sentinel/search?l=YAML&q=mv-expand
Another example of its use is where you have [0] and [1] type entries (often these are 10 or more).
You can use mv-expand to produce the data without the [numbers]
You can play with this example in the Logs Demo:
- Hey Clive_Watson thanks very much for your response. This is so helpful. I will have a look on the Github link and see what I can find as next step as Gary eluded to is connecting with a Join.
Many thanks
