CODEWITHSUNDEEP
azurekqlazure-resource-graph
Xyltic
Xyltic

Reputation: 123

Retrieve last string in KQL query output in Azure

I am running an KQL Query in Azure Resource Graph Explorer:

  resources
  | where type == 'microsoft.network/virtualwans' and tags.CompanyTag =~ 'Company'
  | extend WANname = tostring(name)
  | extend VPNsite = tostring(properties.vpnSites)

And the output of this is

{"id":"/subscriptions/companysubscriptionid/resourceGroups/companyresourcegroupname/providers/Microsoft.Network/vpnSites/NAMEOFTHEWAN"}]

I only want the output to be the 'NAMEOFTHEWAN' out of the string.

My question is how to display the last string information of a KQL-query output?

My thought is to split each / into an Array and then run a follow-up query to show the last array value.

Upvotes: 0

Views: 862

Answers (2)

Xyltic
Xyltic

Reputation: 123

With the reproduction code from @RithwikBojja I modified the code as follows:

  resources
  | where type == 'microsoft.network/virtualwans' and tags.CompanyTag =~ 'CompanyName'
  | extend WANname = tostring(name)
  | extend VPNsite = tostring(properties.vpnSites)
  | extend idemo = split(VPNsite, '/') 
  | extend WAN = array_split(idemo, array_length(idemo) - 1)
  | project WAN[1]

Where I mentioned the VPNsite instead of the project ID in line 4 (as mentioned before, the project ID mentions the ResourceGroupName in my situation)

Upvotes: 0

RithwikBojja
RithwikBojja

Reputation: 11183

Retrieve last string in KQL query output in Azure And the output of this is {"id":"/subscriptions/companysubscriptionid/resourceGroups/companyresourcegroupname/providers/Microsoft.Network/vpnSites/NAMEOFTHEWAN"}] I only want the output to be the 'NAMEOFTHEWAN' out of the string.

My output of id is :

enter image description here

As your ask was to get the end of output string:

I have reproduced in my environment and Got Expected Results as below:

resources
  | where type == 'microsoft.network/virtualwans' 
  | extend WANname = tostring(name)
  | extend VPNsite = tostring(properties.vpnSites)
  | project id
  | extend idemo = split(id, '/') 
  | extend WAN = array_split(idemo, array_length(idemo) - 1)
  | project WAN[1]

Output:

enter image description here

I got the last String of Output and try to follow my code and you will get the result as I have got.

Upvotes: 1

Related Questions