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.
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...
- Clive_Watson_Tech - Yes I see the difference and think I get what's happening. Hopefully I'll shortly arrive with a work example where I need to do this. Thanks again for all your help & patience today.
- Clive_Watson
Final one, if you get chance:
If I enter this:
SecurityAlert
| where TimeGenerated == "2022-02-06T21:33:52.77Z"
| mv-expand Entities
I get this error:
Operator mvexpand: expanded expression expected to have dynamic type
Is this because the Entities Field is a "dynamic Array"? What is it expecting?
