Is there a way see used disk space with the actual disk size of the VMs in a specific subscription in azure?

I am trying to run a query in Azure to show the free disk space or used disk space of each VM in Azure under a specific subscription and I wanted to show in the result the actual disk size of the VM. I need the data to identify the over-allocated resource and to downscale it to minimize the cost. Can someone help me to modify the query inorder to show the actual disk size of the VM in another column aside from the free disk space? or is there other ways to get the data of the disks that I need?

Here is the query that I run from Azure under Monitor>Logs>Query

InsightsMetrics
| where Origin == "vm.azm.ms"
 and Namespace == "LogicalDisk" and Name == "FreeSpacePercentage"
| extend Disk=tostring(todynamic(Tags)["vm.azm.ms/mountId"])
| summarize Disk_Free_Space = avg(Val) by Computer, Disk, _ResourceId
| project Computer, Disk, Disk_Free_Space

and only shows 3 columns Computer Name, Disk, Disk_Free_Space

Solution

Please check if below points can give an idea.

According to Expand virtual hard disks - Azure VM's | Microsoft Docs

When you create a new virtual machine (VM) in a resource group by deploying an image from Azure Marketplace, the default operating system (OS) drive is often 127 GB (some images have smaller OS disk sizes by default). Even though it's possible to add data disks to the VM (the number depends on the SKU you chose)

Try to summarize used memory and free space to get total memory and try to get a column named total disk space or memory out of it.
Hoping this reference Calculating total memory might give an idea.
Also see Azure Resource Graph sample queries in which query looks for virtual machine scale set resources and gets various details including the virtual machine size and the capacity of the scale set.
Azure VM RAM and CPU size depend on the hardware profile chosen for the VM. In this example, we will retrieve VM (TestMachine2k16) hardware profile and then we can find how much RAM or CPU is allocated to it.

To get the Size of the Azure VM,try to use powershell.

 $azvm = Get-AzVM -VMName 'VMname'
$azvm.HardwareProfile.VmSize

We can check the above output size on the Microsoft Azure website to know how much RAM and CPU are associated with it and another way using the PowerShell by using the Get-AZVmSize command.

$vmsize = $azvm.HardwareProfile.VmSize Get-AzVMSize -VMName $azvm.Name -ResourceGroupName $azvm.ResourceGroupName | where{$_.Name -eq $vmsize}

We can monitor VM memory usage performance :Go to your VM -> Monitoring -> Insight
Setup alerts and get notified when a threshold is met - then you can just accomplish by creating a log alert rule. For more information w.r.t it, please refer this document.

References: