KQL to identify which vm has CustomScript extension

Question

I want how many and what VMs have "CustomScript"extension enabled along with the “properties” of that extensions and I have tried this query but didn't extract the custom-extension

      Resources
      | where type == 'microsoft.compute/virtualmachines'
      |         extend
      JoinID = toupper(id),
       OSName = tostring(properties.osProfile.computerName),
OSType = tostring(properties.storageProfile.osDisk.osType),
VMSize = tostring(properties.hardwareProfile.vmSize)
      | join kind=leftouter(
Resources
| where type == 'microsoft.compute/virtualmachines/extensions'
| extend
    VMId = toupper(substring(id, 0, indexof(id, '/extensions'))),
    ExtensionName =~ 'custom-script'
    ) on $left.JoinID == $right.VMId
    | top 5 by name asc

Answer 1

For the custom script extensions, for Windows and Linux, they have slightly different names. This query will return a list of VM with custom script extension and the properties for the VM and the extension.

resources
| where type == 'microsoft.compute/virtualmachines'
| extend
    JoinID = toupper(id),
    OSName = tostring(properties.osProfile.computerName),
    OSType = tostring(properties.storageProfile.osDisk.osType),
    VMSize = tostring(properties.hardwareProfile.vmSize)
| join kind=leftouter(
    resources
    | where type == 'microsoft.compute/virtualmachines/extensions'
    | where name in ("CustomScriptExtension", "CustomScriptForLinux")
    | extend VMId = toupper(substring(id,0,indexof(id,"/",0,-1,9)))
)
on $left.JoinID == $right.VMId