KQL Query - Separate

VolginRnB 256 Reputation points
2021-07-03T04:00:58.063+00:00

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

111485-image.png

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

111512-image.png

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
502 questions
0 comments
Accepted answer
  1. BhargaviAnnadevara-MSFT 5,466 Reputation points
    2021-07-05T05:59:55.163+00:00

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

    111674-image.png

    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.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. PRADEEPCHEEKATLA-MSFT 84,381 Reputation points Microsoft Employee
    2021-07-05T05:29:24.667+00:00

    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

    111669-adx-securityrmd.gif

    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

    111722-image.png

    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.

    0 comments