Virtual Machines

// Azure Resource Graph Query
// Find all VMs that are not associated with a VMSS Flex instance
resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where isnull(properties.virtualMachineScaleSet.id)
| project recommendationId="vm-1", name, id, tags

// Azure Resource Graph Query
// Find all VMs that are not assigned to a Zone
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where isnull(zones)
| project recommendationId="vm-2", name, id, tags, param1="No Zone"

// Azure Resource Graph Query
// Find all VMs using Availability Sets
resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where isnotnull(properties.availabilitySet)
| project recommendationId = "vm-3", name, id, tags, param1=strcat("availabilitySet: ",properties.availabilitySet.id)

// Azure Resource Graph Query
// Find all VMs that do NOT have replication with ASR enabled
// Run query to see results.
resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| project name, id, tags
| join kind=leftouter (
    recoveryservicesresources
    | where type =~ 'Microsoft.RecoveryServices/vaults/replicationFabrics/replicationProtectionContainers/replicationProtectedItems'
    | where properties.providerSpecificDetails.dataSourceInfo.datasourceType =~ 'AzureVm'
    | project id=properties.providerSpecificDetails.dataSourceInfo.resourceId
    | extend name=strcat_array(array_slice(split(id, '/'), 8, -1), '/')
) on name
| where isnull(id1)
| project-away id1
| project-away name1
| project recommendationId = "vm-4", name, id, tags
| order by id asc

// Azure Resource Graph Query
// Find all VMs that are not using Managed Disks
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where isnull(properties.storageProfile.osDisk.managedDisk)
| project recommendationId = "vm-5", name, id, tags

// Azure Resource Graph Query
// Find all VMs that only have OS Disk
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where array_length(properties.storageProfile.dataDisks) < 1
| project recommendationId = "vm-6", name, id, tags

// Azure Resource Graph Query
// Find all VMs that do NOT have Backup enabled
// Run query to see results.
resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| project name, id, tags
| join kind=leftouter (
    recoveryservicesresources
    | where type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems'
    | where properties.dataSourceInfo.datasourceType =~ 'Microsoft.Compute/virtualMachines'
    | project idBackupEnabled=properties.sourceResourceId
    | extend name=strcat_array(array_slice(split(idBackupEnabled, '/'), 8, -1), '/')
) on name
| where isnull(idBackupEnabled)
| project-away idBackupEnabled
| project-away name1
| project recommendationId = "vm-7", name, id, tags
| order by id asc

// Azure Resource Graph Query
// Find all disks with StandardHDD sku attached to VMs
Resources
| where type =~ 'Microsoft.Compute/disks'
| where sku.name == 'Standard_LRS' and sku.tier == 'Standard'
| where managedBy != ""
| project recommendationId = "vm-8", name, id, tags, param1=strcat("managedBy: ", managedBy)

// Azure Resource Graph Query
// Find all VMs that are NOT running
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where properties.extended.instanceView.powerState.displayStatus != 'VM running'
| project recommendationId = "vm-9", name, id, tags

// Azure Resource Graph Query
// Find all VM NICs that do not have Accelerated Networking enabled
resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| mv-expand nic = properties.networkProfile.networkInterfaces
| project name, id, tags, lowerCaseNicId = tolower(nic.id), vmSize = tostring(properties.hardwareProfile.vmSize)
| join kind = inner (
    resources
    | where type =~ 'Microsoft.Network/networkInterfaces'
    | where properties.enableAcceleratedNetworking == false
    | project nicName = split(id, "/")[8], lowerCaseNicId = tolower(id)
    )
    on lowerCaseNicId
| summarize nicNames = make_set(nicName) by name, id, tostring(tags), vmSize
| extend param1 = strcat("NicName: ", strcat_array(nicNames, ", ")), param2 = strcat("VMSize: ", vmSize)
| project recommendationId = "vm-10", name, id, tags, param1, param2
| order by id asc

// cannot-be-validated-with-arg

// Azure Resource Graph Query
// Find all VMs with PublicIPs directly associated with them
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where isnotnull(properties.networkProfile.networkInterfaces)
| mv-expand nic=properties.networkProfile.networkInterfaces
| project name, id, tags, nicId = nic.id
| extend nicId = tostring(nicId)
| join kind=inner (
    Resources
    | where type =~ 'Microsoft.Network/networkInterfaces'
    | where isnotnull(properties.ipConfigurations)
    | mv-expand ipconfig=properties.ipConfigurations
    | extend publicIp = tostring(ipconfig.properties.publicIPAddress.id)
    | where publicIp != ""
    | project name, nicId = tostring(id), publicIp
) on nicId
| project recommendationId = "vm-12", name, id, tags
| order by id asc

// Azure Resource Graph Query
// Provides a list of virtual machines and associated NICs that do have an NSG associated to them and also an NSG associated to the subnet.
Resources
| where type =~ 'Microsoft.Network/networkInterfaces'
| where isnotnull(properties.networkSecurityGroup)
| mv-expand ipConfigurations = properties.ipConfigurations, nsg = properties.networkSecurityGroup
| project nicId = tostring(id), subnetId = tostring(ipConfigurations.properties.subnet.id), nsgName=split(nsg.id, '/')[8]
| parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet
    | join kind=inner (
        Resources
        | where type =~ 'Microsoft.Network/NetworkSecurityGroups' and isnotnull(properties.subnets)
        | project name, resourceGroup, subnet=properties.subnets
        | mv-expand subnet
        | project subnetId=tostring(subnet.id)
    ) on subnetId
    | project nicId
| join kind=leftouter (
    Resources
    | where type =~ 'Microsoft.Compute/virtualMachines'
    | where isnotnull(properties.networkProfile.networkInterfaces)
    | mv-expand nic=properties.networkProfile.networkInterfaces
    | project vmName = name, vmId = id, tags, nicId = nic.id, nicName=split(nic.id, '/')[8]
    | extend nicId = tostring(nicId)
) on nicId
| project recommendationId = "vm-13", name=vmName, id = vmId, tags, param1 = strcat("nic-name=", nicName)

// Azure Resource Graph Query
// Find all VM NICs that have IPForwarding enabled. This feature is usually only required for Network Virtual Appliances
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where isnotnull(properties.networkProfile.networkInterfaces)
| mv-expand nic=properties.networkProfile.networkInterfaces
| project name, id, tags, nicId = nic.id
| extend nicId = tostring(nicId)
| join kind=inner (
    Resources
    | where type =~ 'Microsoft.Network/networkInterfaces'
    | where properties.enableIPForwarding == true
    | project nicId = tostring(id)
) on nicId
| project recommendationId = "vm-14", name, id, tags
| order by id asc

// Azure Resource Graph Query
// Find all VM NICs that have DNS Server settings configured in any of the NICs
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where isnotnull(properties.networkProfile.networkInterfaces)
| mv-expand nic=properties.networkProfile.networkInterfaces
| project name, id, tags, nicId = nic.id
| extend nicId = tostring(nicId)
| join kind=inner (
    Resources
    | where type =~ 'Microsoft.Network/networkInterfaces'
    | project name, id, dnsServers = properties.dnsSettings.dnsServers
    | extend hasDns = array_length(dnsServers) >= 1
    | where hasDns != 0
    | project name, nicId = tostring(id)
) on nicId
| project recommendationId = "vm-15", name, id, tags
| order by id asc

// Azure Resource Graph Query
// Find all Disks configured to be Shared. This is not an indication of an issue, but if a disk with this configuration is assigned to two or more VMs without a proper disk control mechanism (like a WSFC) it can lead to data loss
resources
| where type =~ 'Microsoft.Compute/disks'
| where isnotnull(properties.maxShares)
| project id, name, tags, lowerCaseDiskId = tolower(id), diskState = tostring(properties.diskState)
| join kind = leftouter (
    resources
    | where type =~ 'Microsoft.Compute/virtualMachines'
    | project osDiskVmName = name, lowerCaseOsDiskId = tolower(properties.storageProfile.osDisk.managedDisk.id)
    | join kind = fullouter (
        resources
        | where type =~ 'Microsoft.Compute/virtualMachines'
        | mv-expand dataDisks = properties.storageProfile.dataDisks
        | project dataDiskVmName = name, lowerCaseDataDiskId = tolower(dataDisks.managedDisk.id)
        )
        on $left.lowerCaseOsDiskId == $right.lowerCaseDataDiskId
    | project lowerCaseDiskId = coalesce(lowerCaseOsDiskId, lowerCaseDataDiskId), vmName = coalesce(osDiskVmName, dataDiskVmName)
    )
    on lowerCaseDiskId
| summarize vmNames = make_set(vmName) by name, id, tostring(tags), diskState
| extend param1 = strcat("DiskState: ", diskState), param2 = iif(isempty(vmNames[0]), "VMName: n/a", strcat("VMName: ", strcat_array(vmNames, ", ")))
| project recommendationId = "vm-16", name, id, tags, param1, param2
| order by id asc

// Azure Resource Graph Query
// Find all Disks with "Enable public access from all networks" enabled
resources
| where type =~ 'Microsoft.Compute/disks'
| where properties.publicNetworkAccess == "Enabled"
| project id, name, tags, lowerCaseDiskId = tolower(id)
| join kind = leftouter (
    resources
    | where type =~ 'Microsoft.Compute/virtualMachines'
    | project osDiskVmName = name, lowerCaseOsDiskId = tolower(properties.storageProfile.osDisk.managedDisk.id)
    | join kind = fullouter (
        resources
        | where type =~ 'Microsoft.Compute/virtualMachines'
        | mv-expand dataDisks = properties.storageProfile.dataDisks
        | project dataDiskVmName = name, lowerCaseDataDiskId = tolower(dataDisks.managedDisk.id)
        )
        on $left.lowerCaseOsDiskId == $right.lowerCaseDataDiskId
    | project lowerCaseDiskId = coalesce(lowerCaseOsDiskId, lowerCaseDataDiskId), vmName = coalesce(osDiskVmName, dataDiskVmName)
    )
    on lowerCaseDiskId
| summarize vmNames = make_set(vmName) by name, id, tostring(tags)
| extend param1 = iif(isempty(vmNames[0]), "VMName: n/a", strcat("VMName: ", strcat_array(vmNames, ", ")))
| project recommendationId = "vm-17", name, id, tags, param1
| order by id asc

// Azure Resource Graph Query
// Find all VMs in "NonCompliant" state with Azure Policies
PolicyResources
| where type =~ "Microsoft.PolicyInsights/policyStates" and properties.resourceType =~ "Microsoft.Compute/virtualMachines" and properties.complianceState =~ "NonCompliant"
| project
    policyAssignmentName = properties.policyAssignmentName,
    policyDefinitionName = properties.policyDefinitionName,
    lowerCasePolicyDefinitionIdOfPolicyState = tolower(properties.policyDefinitionId),
    lowerCaseVmIdOfPolicyState = tolower(properties.resourceId)
| join kind = leftouter (
    PolicyResources
    | where type =~ "Microsoft.Authorization/policyDefinitions"
    | project lowerCasePolicyDefinitionId = tolower(id), policyDefinitionDisplayName = properties.displayName
    )
    on $left.lowerCasePolicyDefinitionIdOfPolicyState == $right.lowerCasePolicyDefinitionId
| project policyAssignmentName, policyDefinitionName, policyDefinitionDisplayName, lowerCaseVmIdOfPolicyState
| join kind = leftouter (
    Resources
    | where type =~ "Microsoft.Compute/virtualMachines"
    | project vmName = name, vmId = id, vmTags = tags, lowerCaseVmId = tolower(id)
    )
    on $left.lowerCaseVmIdOfPolicyState == $right.lowerCaseVmId
| extend
    param1 = strcat("AssignmentName: ", policyAssignmentName),
    param2 = strcat("DefinitionName: ", policyDefinitionDisplayName),  // Align to Azure portal's term.
    param3 = strcat("DefinitionID: ", policyDefinitionName)            // Align to Azure portal's term.
| project recommendationId = "vm-18", name = vmName, id = vmId, tags = vmTags, param1, param2, param3

// under-development

// Azure Resource Graph Query
// Check for VMs without Azure Monitoring Agent extension installed, missing Data Collection Rule or Data Collection Rule without performance enabled.
Resources
| where type == 'microsoft.compute/virtualmachines'
| project idVm = tolower(id), name, tags
| join kind=leftouter (
    InsightsResources
    | where type =~ "Microsoft.Insights/dataCollectionRuleAssociations" and id has "Microsoft.Compute/virtualMachines"
    | project idDcr = tolower(properties.dataCollectionRuleId), idVmDcr = tolower(substring(id, 0, indexof(id, "/providers/Microsoft.Insights/dataCollectionRuleAssociations/"))))
on $left.idVm == $right.idVmDcr
| join kind=leftouter (
    Resources
    | where type =~ "Microsoft.Insights/dataCollectionRules"
    | extend
        isPerformanceEnabled = iif(properties.dataSources.performanceCounters contains "Microsoft-InsightsMetrics" and properties.dataFlows contains "Microsoft-InsightsMetrics", true, false),
        isMapEnabled = iif(properties.dataSources.extensions contains "Microsoft-ServiceMap" and properties.dataSources.extensions contains "DependencyAgent" and properties.dataFlows contains "Microsoft-ServiceMap", true, false)//,
    | where isPerformanceEnabled or isMapEnabled
    | project dcrName = name, isPerformanceEnabled, isMapEnabled, idDcr = tolower(id))
on $left.idDcr == $right.idDcr
| join kind=leftouter (
    Resources
        | where type == 'microsoft.compute/virtualmachines/extensions' and (name contains 'AzureMonitorWindowsAgent' or name contains 'AzureMonitorLinuxAgent')
        | extend idVmExtension = tolower(substring(id, 0, indexof(id, '/extensions'))), extensionName = name)
on $left.idVm == $right.idVmExtension
| where isPerformanceEnabled != 1 or (extensionName != 'AzureMonitorWindowsAgent' and extensionName != 'AzureMonitorLinuxAgent')
| project recommendationId = "vm-20", name, id = idVm, tags, param1 = strcat('MonitoringExtension:', extensionName), param2 = strcat('DataCollectionRuleId:', idDcr), param3 = strcat('isPerformanceEnabled:', isPerformanceEnabled)

// Azure Resource Graph Query
// Find all Virtual Machines without diagnostic settings enabled/with diagnostic settings enabled but not configured both performance counters and event logs/syslogs.
resources
| where type =~ "microsoft.compute/virtualmachines"
| project name, id, tags, lowerCaseVmId = tolower(id)
| join kind = leftouter (
    resources
    | where type =~ "Microsoft.Compute/virtualMachines/extensions" and properties.publisher =~ "Microsoft.Azure.Diagnostics"
    | project
        lowerCaseVmIdOfExtension = tolower(substring(id, 0, indexof(id, "/extensions/"))),
        extensionType = properties.type,
        provisioningState = properties.provisioningState,
        storageAccount = properties.settings.StorageAccount,
        // Windows
        wadPerfCounters = properties.settings.WadCfg.DiagnosticMonitorConfiguration.PerformanceCounters.PerformanceCounterConfiguration,
        wadEventLogs = properties.settings.WadCfg.DiagnosticMonitorConfiguration.WindowsEventLog,
        // Linux
        ladPerfCounters = properties.settings.ladCfg.diagnosticMonitorConfiguration.performanceCounters.performanceCounterConfiguration,
        ladSyslog = properties.settings.ladCfg.diagnosticMonitorConfiguration.syslogEvents
    | extend
        // Windows
        isWadPerfCountersConfigured = iif(array_length(wadPerfCounters) > 0, true, false),
        isWadEventLogsConfigured = iif(isnotnull(wadEventLogs) and array_length(wadEventLogs.DataSource) > 0, true, false),
        // Linux
        isLadPerfCountersConfigured = iif(array_length(ladPerfCounters) > 0, true, false),
        isLadSyslogConfigured = isnotnull(ladSyslog)
    | project
        lowerCaseVmIdOfExtension,
        extensionType,
        provisioningState,
        storageAccount,
        isPerfCountersConfigured = case(extensionType =~ "IaaSDiagnostics", isWadPerfCountersConfigured, extensionType =~ "LinuxDiagnostic", isLadPerfCountersConfigured, false),
        isEventLogsConfigured = case(extensionType =~ "IaaSDiagnostics", isWadEventLogsConfigured, extensionType =~ "LinuxDiagnostic", isLadSyslogConfigured, false)
    )
    on $left.lowerCaseVmId == $right.lowerCaseVmIdOfExtension
| where isempty(lowerCaseVmIdOfExtension) or provisioningState !~ "Succeeded" or not(isPerfCountersConfigured and isEventLogsConfigured)
| extend
    param1 = strcat("DiagnosticSetting: ", iif(isnotnull(extensionType), strcat("Enabled, partially configured (", extensionType, ")"), "Not enabled")),
    param2 = strcat("ProvisioningState: ", iif(isnotnull(provisioningState), provisioningState, "n/a")),
    param3 = strcat("storageAccount: ", iif(isnotnull(storageAccount), storageAccount, "n/a")),
    param4 = strcat("PerformanceCounters: ", case(isnull(isPerfCountersConfigured), "n/a", isPerfCountersConfigured, "Configured", "Not configured")),
    param5 = strcat("EventLogs/Syslogs: ", case(isnull(isEventLogsConfigured), "n/a", isEventLogsConfigured, "Configured", "Not configured"))
| project recommendationId = "vm-21", name, id, tags, param1, param2, param3, param4, param5

// Azure Resource Graph Query
// Find VMS that do not have maintenance configuration assigned
Resources
| extend resourceId = tolower(id)
| project name, location, type, id, tags, resourceId, properties
| where type =~ 'Microsoft.Compute/virtualMachines'
| join kind=leftouter (
maintenanceresources
| where type =~ "microsoft.maintenance/configurationassignments"
| project planName = name, type, maintenanceProps = properties
| extend resourceId = tostring(maintenanceProps.resourceId)
) on resourceId
| where isnull(maintenanceProps)
| project recommendationId = "vm-22",name, id, tags
| order by id asc

// Azure Resource Graph Query
// Find all VMs using A or B series families
resources
| where type == 'microsoft.compute/virtualmachines'
| where properties.hardwareProfile.vmSize contains "Standard_B" or properties.hardwareProfile.vmSize contains "Standard_A"
| project recommendationId = "vm-23", name, id, tags, param1=strcat("vmSku: " , properties.hardwareProfile.vmSize)

// Azure Resource Graph Query
// Find all VMs that have an attached disk that is not in the Premium or Ultra sku tier.

resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| extend lname = tolower(name)
| join kind=leftouter(resources
    | where type =~ 'Microsoft.Compute/disks'
    | where not(sku.tier =~ 'Premium') and not(sku.tier =~ 'Ultra')
    | extend lname = tolower(tostring(split(managedBy, '/')[8]))
    | project lname, name
    | summarize disks = make_list(name) by lname) on lname
| where isnotnull(disks)
| project recommendationId = "vm-24", name, id, tags, param1=strcat("AffectedDisks: ", disks)

// under-development

// under-development