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.
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
- now you are past the rabbit in the headlight stage I see 😉
- 🙂 Yes - curiosity gets the better of me!!
So If I do this:
SecurityIncident
| where TimeGenerated == "2/4/2022, 11:49:50.950 AM"
| mv-expand AdditionalData
| project AdditionalData
I get a single nice clean column of the expanded data.
Sometimes, I see a =tostring in the examples, eg:
SecurityIncident
| where TimeGenerated == "2/4/2022, 11:49:50.950 AM"
| mv-expand AdditionalData
| project AdditionalData = tostring(AdditionalData)
It makes no difference to the output that I see, so what is happening?it could make a difference later on, try these three examples to see the difference
SecurityIncident | limit 1 | mv-expand AdditionalData | project AdditionalData | getschema SecurityIncident |limit 1 | mv-expand AdditionalData | project AdditionalData = tostring(AdditionalData) | getschema SecurityIncident | limit 1 | mv-expand AdditionalData to typeof(string) | project AdditionalData | getschema"later on" you may need the column as a string (maybe as part of a summarize), in other cases you need it to remain dynamic - maybe you need to find a position in the array or the length etc...
