Forum Discussion
Solved
mv-expand - I cannot make it work!!
Can anyone spare anytime to give me a basic example of how to use mv-expand please, so that I can then expand on it! (See what I did there 
) I just don't get it. I understand that it can be used to extract a value from an array, but in my fiddling it's not happening. I have looked at the docs but the examples just don't relate\click with me.
I've been enjoying TeachJing Youtube lessons - but I haven't found one that covers this command.
I'm just looking for the most minimal lines so I can build from it.
Many thanks (Soz for the stupid question!)
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 Clive_Watson GaryBushey
In your experience with mv-expand have you had issues with the json being truncated so mv-expand doesn't work?
I made a logic app to pull in EDR alerts from carbon black and the alerts can be very long (over 1600 lines of json).
Are you aware of any limits and if there are any can that be fixed in my logic app?
Or is it possible my json just has a funky ending that needs to be truncated?
eg: here's my query:
CarbonBlack_CL
|mv-expand todynamic(results_s) to typeof(string)
|project results_sAnd here's the tail end of the mvexpand on every results_s value. As you can see, that ",{" shouldn't be there:
<main body of json>
...
"ENUMERATE_PROCESSES"
]
},
{- Only the limits documented: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/mvexpandoperator
As you converted it to "string", maybe that's the restriction, if 1600 lines > 1MB? https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/scalar-data-types/stringThanks Clive_Watson
Today I gave up on the logic app because I figure out how to get the Carbon Black Cloud Azure function data connector to work :).
if you're interested:Thanks very much for your reply.
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.
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
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
