Forum Discussion
VM details query
Hi Team,
Trying to query VM details using KQL but unable to include different thing in query.
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
11 Replies
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
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.
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
