mv-expand - I cannot make it work!!

[CodnChips]

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!)

[#2 · Accepted solution]

@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 AlertIds

This 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.

[#3]

@CodnChips 

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:

Go to Log Analytics and run query

[#4]

Hi @Gary Bushey 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, thanks

[#5]

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

[#6]

@CodnChips 

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

[#7]

@Clive_Watson Thanks Clive - I've got to start smaller! I'm like rabbit in the headlights with those examples!! :D

[#8]

That's interesting - on one of the examples, it shows you can do "nested" mv-expands

[#9]

now you are past the rabbit in the headlight stage I see ;)

[#10]

:) 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?

[#11]

@CodnChips 

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... 

[#12]

@clivewatsonQuorumCyber - 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.

[#13]

@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?

[#14]

@CodnChips 

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 work

SecurityAlert
| 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"

[#15]

@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) :)

[#16]

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

[#17]

@Clive_Watson Perfect, thanks!

[#18]

@CodnChips @Clive_Watson @Gary Bushey 

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"
]
},
{

[#19]

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/string

[#20]

Thanks @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:

https://simple-security.ca/2023/05/02/cheat-sheet-for-configuring-carbon-black-cloud-edr-for-sentine... 

Thanks very much for your reply.