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 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_s
And 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.
- but you bring up an interesting point, I guess I should have expanded the 'big' json and then applied strings to the individual fields....
