KQL Query - Separate

Question

Hello Guys,

Anyone can help me please ? I am running a simple KQL query in Azure Log Analytics Workspace and I would want "breake or separate" the result in "/"

For example the query below show me the result "/subscriptions/c634c2a4-0f67-4654-aade-f7961b530b22/resourceGroups/RG-VMS/providers/Microsoft.Compute/virtualMachines/prd-app-ml".

I would like create new colunms "resourceGroups" and "VM" like example image below, is it possible ?

Answer 1

@VolginRnB Indeed. You can use the split() function in KQL to split the string column by a specified delimiter and then extract and extend the desired parts into separate columns.

For example, calling the split() function on a string like

/subscriptions/mySubscriptionId/resourcegroups/myResourceGroup/providers/Microsoft.Compute/virtualMachines/myVM

would return:

["","subscriptions","mySubscriptionId","resourcegroups","myResourceGroup","providers","Microsoft.Compute","virtualMachines","myVM"]

You can then extend the ResourceGroup and ResourceName using the extend operator shown as under:

For your use case, a query like the following should help:

   SecurityRecommendation  
   | project AssessedResourceId  
   | extend SplitAll=split(AssessedResourceId, '/')  
   | extend ResourceGroup=SplitAll[4], VMName=SplitAll[-1]  
   | project-away SplitAll, AssessedResourceId //optional  

Feel free to modify this as required. Also check the parse operator in KQL for other regex options for parsing string expressions.

Hope this helps. Do let us know if you have further questions.

Answer 2

Hello @VolginRnB ,

Thanks for the question and using MS Q&A platform.

You may use the parse operator to achieve above requirement:

SecurityRecommendation  
| project AssessedResourceId  
| parse AssessedResourceId with * "resourceGroups/" resourceGroups "/" * "virtualMachines/" virtualMachines  

If you want to pass as string, you may try the below:

let str = "/subscriptions/c634c2a4-0f67-4654-aade-f7961b530b22/resourceGroups/RG-VMS/providers/Microsoft.Compute/virtualMachines/prd-app-ml";  
print str  
| parse str with * "resourceGroups/" resourceGroups "/" * "virtualMachines/" virtualMachines  

For more details, refer to Azure Data Explorer - parse operator.

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please do click on the"Accept the answer" if the information helped you. This will help us and others in the community as well.