Using KQL to find duplicate VM names in Azure

Darrin Miskiewicz 1 Reputation point
2022-04-28T15:23:39.85+00:00

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
Microsoft Graph
Microsoft Graph
A Microsoft programmability model that exposes REST APIs and client libraries to access data on Microsoft 365 services.
11,323 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Rahul Gosavi 166 Reputation points
    2022-08-24T10:26:34.027+00:00

    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.

    0 comments No comments