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
Mar 11, 2020
Rahul_Mahajan

Those data types are arrays, so need to be strings at the end of a Summarize - I used https://docs.microsoft.com/en-us/azure/kusto/query/tostringfunction to allow this

https://ms.portal.azure.com#@72f988bf-86f1-41af-91ab-2d7cd011db47/blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade/resourceId/%2FDemo/source/LogsBlade.AnalyticsShareLinkToQuery/q/H4sIAAAAAAAAA2WQS0%252FDMBCE7%252F0VS08g%252BciVQ2hFiEQAkSpX5CZDYjV%252ByA8qR%252Fx4TAhCafbmndlvZ12XOy1N8LCbLzr3sKD3NzgdbIOipas72m6T4oKU3IoRdIx0EBI5FCz3aBk9auefuQSjbAwWheQdqlOYn3%252Bw3Opg2IaWNVmedMO90GqeqNIaRjsTHKM9DFQL1cSsg%252FI1rJuMl5zXPjrR8KGE1DaW94xezE8%252BoboqOg%252F5wKUY4rodhuE3%252ByWxFtaHBORNLxQO0STP3BPjFLfy6f7VRT79hk3868J83mZta%252BEc3A1bCnt88DD4PBHOPK7kKhwVfMndaSGlMP%252FEb%252FiKGa%252B6AQAA/timespan/P1D

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

Former Employee

Mar 12, 2020

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

https://ms.portal.azure.com#@72f988bf-86f1-41af-91ab-2d7cd011db47/blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade/resourceId/%2FDemo/source/LogsBlade.AnalyticsShareLinkToQuery/q/H4sIAAAAAAAAA2VSXW%252FaQBB8R%252BI%252FbKw8gGTFrdRXRyJEJUglrQLiNTrshbvG96G7PahRfnzXNoZS%252FGKfd252dnayDCrcEqzEpsLhYL2YWu0ioR8OPuEg0SO8v2Gw0Rc4L%252BEuhyRpSiFqLbw6ImxqWCmNMzToBWGZwosN9Co0pjA5Ro9zLXa4%252FIinY8828za6dDiA66fF%252FLCFIGXN6cqS%252B6QwdTGk8IwOTYmmqCc7NLRGH1rgDdEvWQdViGqB2vp68ZTCT9coVGa3rAOh%252Fi60qurb37GqOvU3lGvlKTKjKKQyuKodg07%252F1LEVvCS24HYoYkc8dxjN3f7bpCw9hoBhnF4XnnErYkUzpjiI%252Bqa8jBuDtBDh46rEai6Mw0GWgVc7ed7oJ%252Fy2ysCo0%252FSCwtMGBXXHLOvfMIfCmqBK9CBAdxNyE9gg%252BGgMdwK1hQOCFHtkiLNBkeJP2VMyQTQE3IwkghOBQPKm4dxiJVUAa6oaCAOFFiaaJYKx1J4C%252Bn0TvX%252B19TG8Chk88k07%252BirHPegSyMuQ01ZQ3gkbjZuoXuI9Zilw34T%252FoQ8s5Dnct%252B499LhOeWuh7Qa7pJvptHCusYYkz89uNYDzVdNQNhvAP8SZBRXeTk7moNR29J%252FOx%252FwLd0uTEyhJk1e2Zcu1Mhn%252FBWaZTUmoAwAA

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

Rahul_Mahajan
Brass Contributor
Mar 17, 2020
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;
- CliveWatson
  Former Employee
  Mar 17, 2020
  Rahul_Mahajan
  
  Like this? https://ms.portal.azure.com#@72f988bf-86f1-41af-91ab-2d7cd011db47/blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade/resourceId/%2FDemo/source/LogsBlade.AnalyticsShareLinkToQuery/q/H4sIAAAAAAAAA2WT3YrbMBCF7w15h2mgYINLuqVXhRTSDZsG6jbUIbdFkSe2Gmlk9LNBYR%252B%252BspPdxLWuLM3RN0cz49kMJB4cbNle4iTZFY9atd6hmSQvcGrQIAhL2qFqXUj%252F%252FEarveG4rrJOYL1SzIgzwj7AVihcIaFhDqscvmvrfjKFOSzO3uBasRrLo79uX0Ero32bTxIYrl7zQ3PmhKbrlTLmyeGx9TaHJbZIFRIPixrJ7dDYXjgCbZpgBWeyQKVNKL7l8KvtHAqqy2Djs56YEjKMj72UF%252Fcj5E4Y5yOR8UYQbkMbRdczce4Nly6WYPwoFytiYoZ03T5%252FXlSVQWvRZvkwsMQD89KtIuLEwihc%252Bj2hK5g9DkLRzT3x1sXZDIyom7cOv8BfLQjSi7sNmsPl67Xbgy7CVyB9Sj88fFSCMmBURbKnCO5qA%252FM5TN%252FDk0GEsmUcp71iTdYx4thL3nFNjgmyMF1uiukwV%252FQukdL7C1mEfhpjbpQvb4zb8HUOlsIeexMwh5Q91%252BnV6I5Jj1nWDeheUPrfkD40d8XKBzknSQaa4P5%252F0KZCMxp1uBGgQsuTf9RyQqRUAwAA
  
  // 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)

Forum Discussion

VM details query

Resources