Mar 11 2020
02:17 AM
- last edited on
Apr 08 2022
10:19 AM
by
TechCommunityAP
Mar 11 2020
02:17 AM
- last edited on
Apr 08 2022
10:19 AM
by
TechCommunityAP
Hi Team,
Trying to query VM details using KQL but unable to include different thing in query.
Mar 11 2020 08:32 AM
Solution
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
Mar 12 2020 02:20 AM - edited Mar 12 2020 02:23 AM
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.
Mar 12 2020 05:15 AM
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
Mar 16 2020 09:11 PM
regarding VM state its seems difficult to get the state of VMs with output what we are getting using
or Stopped.
If not possible, can you help to integrate below query details for disk space with VMComputer output
Mar 17 2020 03:08 AM
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)
Mar 17 2020 04:24 AM
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
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
Mar 17 2020 05:45 AM - edited Mar 17 2020 05:45 AM
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.
Mar 17 2020 06:45 AM
For a single line output you can try, a query like this
Aug 24 2023 11:27 PM
Aug 25 2023 02:33 AM
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
Mar 11 2020 08:32 AM
Solution
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