Using KQL to find duplicate VM names in Azure

Question

I have a need to use a KQL query to find duplicate Azure VM names. My query is below and I am struggling to find a way to return only the duplicate values with the associated RG and Sub name. Is this possible in KQL?

resources
| where type == "microsoft.compute/virtualmachines" 
| join kind=inner (
    ResourceContainers
    | where type =~ 'microsoft.resources/subscriptions'
    | project SubName=name, subscriptionId)
on subscriptionId    
| project name, resourceGroup, subscriptionId, SubName
| summarize by name,SubName,resourceGroup

Answer 1

You can re-write you query in below manner:

resources
| where type == "microsoft.compute/virtualmachines"
| join kind=inner (
ResourceContainers
| where type =~ 'microsoft.resources/subscriptions'
| project SubName=name, subscriptionId)
on subscriptionId
| project name, resourceGroup, subscriptionId, SubName
| summarize count() by name,SubName,resourceGroup
| where count_>1

this will give you Name, SubName,resourceGroup of duplicate VMs.

If it worked for you, just Accept this as an answer.