Retrieve last string in KQL query output in Azure

Question

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.

Try this ```resources | where type == 'microsoft.network/virtualwans' and tags.CompanyTag =~ 'Company' | extend WANname = tostring(name) | extend VPNsite = tostring(properties.vpnSites) | extend idParts = split(id, '/') | extend WAN = array_element_at(idParts, array_length(idParts) - 1) | project WAN``` — RithwikBojja, Mar 06 '23 at 08:53
I get this error message: Details: Query is invalid. Please refer to the documentation for the Azure Resource Graph service and fix the error before retrying. (Code:InvalidQuery) Unknown function: 'array_element_at'. (Code:UnknownFunction) Failed to analyze expression for extended column ["WAN"] (Code:InvalidExtendedExpression) — Xyltic, Mar 06 '23 at 09:07
When i run your query https://i.imgur.com/2hTOMSR.png I get this output there you can easily select Name right? https://i.imgur.com/C5PAJWc.png — RithwikBojja, Mar 06 '23 at 09:12
You are right, the WANname / name variable actually consists of the resource group name unfortunately (something that the company decided to use for). It is all about the VPNsite variable instead. https://imgur.com/a/BQNm6Pr Here a POV https://imgur.com/a/Cv66YB1 — Xyltic, Mar 06 '23 at 09:23
Based on the second link I provide, you can see the long-string where the company name is mentioned at the end. I only want that part instead of the whole output and since your answer gave an error I cannot see it as an answer. — Xyltic, Mar 06 '23 at 09:30
I am saying to use this query ```resources | where type == 'microsoft.network/virtualwans' | extend WANname = tostring(name) | extend VPNsite = tostring(properties.vpnSites) | project name ``` https://i.imgur.com/J4wahwV.png — RithwikBojja, Mar 06 '23 at 09:38
The name is of the project is same as the resource group name https://imgur.com/a/wb993tX - Hence, I am asking to retrieve somehow the last string of the KQL-query output since it mentions the name of the VPN site name. — Xyltic, Mar 06 '23 at 09:44

score 1 · Accepted Answer · answered Mar 06 '23 at 10:17

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.

Glad that it Solved your problem. – RithwikBojja Mar 06 '23 at 10:56 — RithwikBojja, Mar 06 '23 at 10:56

score 0 · Answer 2 · answered Mar 06 '23 at 10:59

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)

Retrieve last string in KQL query output in Azure

2 Answers2