Forum Discussion

Rahul_Mahajan's avatar
Rahul_Mahajan
Brass Contributor
Mar 11, 2020

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, 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.
  • 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)

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

    • Rahul_Mahajan's avatar
      Rahul_Mahajan
      Brass Contributor

      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.

      • CliveWatson's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft

        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






         

Resources