Need help improving my KQL query for Azure VM disk sizes

Question

Need help improving my KQL query for Azure VM disk sizes

Joel Pangilinan 35

Hi,

I have this KQL query wherein it list all VMs in my environment with OS and data disk sizes. However, whenever I include the 2 lines code which is highlighted in bold below, the output of the query excludes VMs with only OS disk. Basically the query shows only VMs with both OS and data disks. I want to have an output that includes VMs with OS disks. Can you please help improve on my query. Thanks.

| mv-expand DataDiskSizeInGB = properties.storageProfile.dataDisks

| extend DataDiskInGB = DataDiskSizeInGB.diskSizeGB

Resources
| where type in~ (
        "microsoft.compute/virtualmachines",
        "microsoft.scvmm/virtualmachines",
		"microsoft.connectedvmwarevsphere/virtualmachines",
		"microsoft.azurestackhci/virtualmachines")
| extend
    OSType = tostring(properties.storageProfile.osDisk.osType),
    OSDiskSizeInGB = tostring(properties.storageProfile.osDisk.diskSizeGB)
| mv-expand DataDiskSizeInGB = properties.storageProfile.dataDisks
| extend DataDiskInGB = DataDiskSizeInGB.diskSizeGB
| project id, name, location, resourceGroup, subscriptionId, OSType, OSDiskSizeInGB, DataDiskSizeInGB, properties

SaiKishor-MSFT 17,336 Reputation points Moderator

2023-02-21T19:37:35.6666667+00:00

@Joel Pangilinan I am reaching out to see if you have any further questions. If so, please let us know and we will be glad to assist further. If the below answer was helpful, please make sure to Accept it as answer as it helps other members in the community. Thank you!
Sumarigo-MSFT 47,506 Reputation points Microsoft Employee Moderator

2023-03-06T11:36:51.9566667+00:00

@Joel Pangilinan Just checking in to see if the below answer helped. If this answers your query, do click "Accept the answer”, which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

1 answer

Your answer

SaiKishor-MSFT 17,336 Reputation points Moderator

2023-02-21T19:37:35.6666667+00:00

@Joel Pangilinan I am reaching out to see if you have any further questions. If so, please let us know and we will be glad to assist further. If the below answer was helpful, please make sure to Accept it as answer as it helps other members in the community. Thank you!
Sumarigo-MSFT 47,506 Reputation points Microsoft Employee Moderator

2023-03-06T11:36:51.9566667+00:00

@Joel Pangilinan Just checking in to see if the below answer helped. If this answers your query, do click "Accept the answer”, which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Answer 1

Hello @Joel Pangilinan ,

Can you try out the below query:

Resources
| where type in~ (
        "microsoft.compute/virtualmachines",
        "microsoft.scvmm/virtualmachines",
		"microsoft.connectedvmwarevsphere/virtualmachines",
		"microsoft.azurestackhci/virtualmachines")
        |  extend properties=parse_json(properties)
        | extend  name, DataDiskSizeInGB=properties.storageProfile.dataDisks[0].diskSizeGB , OSDiskSizeInGB=properties.storageProfile.osDisk.diskSizeGB,OSType = tostring(properties.storageProfile.osDisk.osType)
        | project id, name, location, resourceGroup, subscriptionId, OSType, OSDiskSizeInGB, DataDiskSizeInGB, properties

Below is the output of above query in my local testing:

User's image

Share via

Need help improving my KQL query for Azure VM disk sizes

1 answer

Your answer