Forum Discussion

Rahul_Mahajan's avatar
Rahul_Mahajan
Brass Contributor
Mar 11, 2020
Solved

VM details query

Hi Team,   Trying to query VM details using KQL but unable to include different thing in query.   VMComputer | where _ResourceId != "" | summarize by TimeGenerated, HostName, AzureImageSku, Az...
azure monitor
Query Language
  • CliveWatson's avatar
    CliveWatson
    Mar 11, 2020

    Rahul_Mahajan 

     

    Those data types are arrays, so need to be strings at the end of a Summarize - I used tostring to allow this 

     

    Go to Log Analytics and run query

     

    VMComputer
| where _ResourceId != ""
| summarize by TimeGenerated, HostName, AzureImageSku, AzureResourceGroup,
               AzureLocation, AzureSize, Cpus, DependencyAgentVersion, 
               PhysicalMemoryMB, OperatingSystemFamily, OperatingSystemFullName, 
               VirtualMachineType, VirtualizationState,
               tostring(Ipv4Addresses), tostring(Ipv4DefaultGateways), tostring(Ipv4SubnetMasks), tostring(MacAddresses)

    I 

CliveWatson's avatar
CliveWatson
Silver Contributor
Mar 17, 2020

@Rahul_Mahajan 


Depending on your use case, if you just wanted to visually see this type of data a Azure Monitor Workbook should help.

 

Please look for and open the file (RAW mode is best) at this link: https://github.com/CliveW-MSFT/KQLpublic/blob/master/KQL/Workbooks/AzureVMwithDiskspace.workbook 

you just need to COPY all the file content (Ctrl+A then Ctrl+C)

 

Installation 

  1. [Copy] the workbook file content (these are JSON files), open Azure Monitor Workbooks (from portal.azure.com) – open the “empty” Azure Monitor Workbook, in “advanced edit” mode (press the </> icon for advanced edit ). Please [paste] over any json that exists.
  2. Then Press [apply] then [Done Editing]

This should look like 

 

You can use the Search bar (circled in red) to filter on state, like Succeeded or Deallocated etc...

 

When you highlight a Virtual Machine, click on a ROW (and if there is data) you get the VM Connection info an or the Disk Space 

 

 

Rahul_Mahajan's avatar
Rahul_Mahajan
Brass Contributor
Mar 17, 2020

@Clive Watson 

 

Will try it 🙂

For now, I have changed the query like :

VMComputer
| where isnotempty(_ResourceId)
| join kind= innerunique (
Perf
| where TimeGenerated > now(-10min) and CounterName == "% Free Space" and InstanceName !contains "DPM"
| where strlen(InstanceName) ==2 and InstanceName contains ":"
| summarize FreeDiskSpace = (avg(CounterValue)) by bin(TimeGenerated, 1h), InstanceName,Computer
) on Computer
| summarize by TimeGenerated, Computer, AzureImageSku, AzureResourceGroup,
AzureLocation, AzureSize, Cpus, DependencyAgentVersion,
PhysicalMemoryMB, OperatingSystemFamily, OperatingSystemFullName,
VirtualMachineType, VirtualizationState, tostring(Ipv4Addresses), tostring(Ipv4DefaultGateways), tostring(Ipv4SubnetMasks), tostring(MacAddresses), InstanceName, FreeDiskSpace

Here there are multiple records are showing as different drives for same server.

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Aug 25, 2023

    harisankaran 

     

    Powerstate is exposed in ARG (so you can use KQL).  I think time Created is also timeUpdated but you'd have to confirm.

     



    resources
| where type == "microsoft.compute/virtualmachines"
| extend extensionType = properties.type, 
	status = properties.provisioningState,
	timeCreated = properties.timeCreated,
	version = properties.typeHandlerVersion,
    PowerStatus = properties.extended.instanceView.powerState.displayStatus,
	OSType = properties.storageProfile.osDisk.osType
| project ComputerName=name, PowerStatus,timeCreated, status, version, OSType, ['id'], properties

     

  • harisankaran's avatar
    harisankaran
    Copper Contributor
    Aug 25, 2023
    I want to find out when the vm was deallcated using kql, can you help me.

Resources