SOLVED

VM details query

Brass Contributor

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, AzureResourceGroup, AzureLocation, AzureSize, Cpus, DependencyAgentVersion, PhysicalMemoryMB, OperatingSystemFamily, OperatingSystemFullName, VirtualMachineType, VirtualizationState
 
Unable to include IPaddress details in it which can be seen using :
 
| project Computer, Ipv4Addresses, Ipv4DefaultGateways, Ipv4SubnetMasks, MacAddresses
 
Also, it doesn't have a state of VM like Running or Stopped
 
Can someone help to include them in one query?
 
Thanks in advance.
11 Replies
best response confirmed by Rahul_Mahajan (Brass Contributor)
Solution

@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)

Thanks ton @Deleted

One more thing.. can you please help to get VM disk size and state added like stopped or running as I am not able to find any details in it for that.

 

or suggest any other query which will have all these details.

@Rahul_Mahajan 

 

For running state if you don't have it in any of your tables (its not a default for logging) then you have to create a test.  Here I'm joining the data to the Heartbeat Table as all Log Analytics computers will have this (you may wish to use another Table instead or in addition).  Its a basic test as a Server could be up but the agent not sending data.

 

// left Table
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)
// right Table
| join (
    Heartbeat
    //
    // I consider a machine to be running if we have a positive heartbeat count in the past hour 
    // This only tests the agent not the server
    //
    | where TimeGenerated > ago(1h)
    | summarize  HeartbeatCount = count() by Computer
) on $left.HostName == $right.Computer   // join on the HostName, by mapping that to the Computer name 
| extend isRunning = iif(HeartbeatCount >=0   ,"Running","Not found")

 

Go to Log Analytics and run query

 

e.g.

 

HostName isRunning HeartbeatCount
rancher-node-3 Running 60
gangams-kind-k8s-cluster-master Running 60
InfraScaleVMs Running 60
demo2 Running 60
MarketingLinux1 Running 60
node-4 Running 30
rancher-node-1 Running 60
rancher-node-2 Running 60
ContosoASCAlert Running 59

 

For disk size you will need a counter - do you have any for disk, normally these are under the Perf table?

There is an example query when you OPEN a new Tab.

// Top 10 computers with the highest disk space
// Show the top 10 computers with the highest available disk space
Perf
| where CounterName == "Free Megabytes" and InstanceName == "_Total" 
| summarize arg_max(TimeGenerated, *) by Computer
| top 10 by CounterValue






 

@CliveWatson 

 

regarding VM state its seems difficult to get the state of VMs with output what we are getting using 

 

VMComputer
| where TimeGenerated > ago(1d)
| 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)
 
Like I want to get VM status in front of the above query output as Running or Stopped (Deallocated)

or Stopped.

 

If not possible, can you help to integrate below query details for disk space with VMComputer output

 

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), Computer, InstanceName
| sort by TimeGenerated, Computer desc;
 
 
 

@Rahul_Mahajan 

 

Like this? Go to Log Analytics and run query

// left Table
VMComputer
| where isnotempty(_ResourceId)
| summarize by TimeGenerated, HostName, AzureImageSku, AzureResourceGroup,
               AzureLocation, AzureSize, Cpus, DependencyAgentVersion, 
               PhysicalMemoryMB, OperatingSystemFamily, OperatingSystemFullName, 
               VirtualMachineType, VirtualizationState,
               tostring(Ipv4Addresses), tostring(Ipv4DefaultGateways), tostring(Ipv4SubnetMasks), tostring(MacAddresses), Computer
// right Table
| join (
    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), Computer, InstanceName
) on Computer
| order by TimeGenerated , Computer desc

 

or (with running info)

 

@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 

 

pic1.jpg

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 

 

pic2.jpg

 

@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.

@Rahul_Mahajan 

 

For a single line output you can try, a query like this

Go to Log Analytics and run query

Perfect :)

Thanks @CliveWatson
I want to find out when the vm was deallcated using kql, can you help me.

@harisankaran 

 

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

Clive_Watson_0-1692955976264.png

 



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

 

1 best response

Accepted Solutions
best response confirmed by Rahul_Mahajan (Brass Contributor)
Solution

@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)

View solution in original post