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.
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?
- Clive_Watson Perfect, thanks!
- Glad to help 😉
You can also try https://github.com/rod-trent/MustLearnKQL for some more tips, and https://github.com/rod-trent/AddictedtoKQL when it releases.
#MustLearnKQL #KQL - Clive_Watson
Aaaah - see that makes sense. The help files\docs just don't present the information in a digestible format. Thankyou so much for your clear responses - I wouldn't have extracted that understanding from the docs (Which I appreciate is my own shortcoming) 🙂 Its a string, and mv-expand needs a Dynamic array - getschema will confirm this for you:
SecurityAlert | getschema | where ColumnName =="Entities"mv-expand operator - Azure Data Explorer | Microsoft Docs
Expands multi-value dynamic arrays or property bags into multiple records.
So you need to switch the string to Dynamic for mv-expand to workSecurityAlert | limit 1 | mv-expand todynamic(Entities)And at that point you decide if you need the results as Dynamic or if you need them as a string (and that depends on the commands, if any, you plan to run on the filtered data)
SecurityAlert | limit 1 | mv-expand todynamic(Entities) //to typeof(string) | getschema | where ColumnName =="Entities"
