3 Replies Latest reply on Apr 10, 2011 11:53 PM by netlogix

    Virtual machines queries

    contracer

      Hi Thwack Forum,

      Please, I´m looking for sql queries to show me:

      - All virtual machine on Up status;

      - All virtual machines on warning status;

      - All virtual machines on Down status.

      Thanks again.

        • Re: Virtual machines queries
          KCWayner

          This query gives you one report will show all the vm's and their state.  We don't have it broken down by their status, just by the host system.

           

          SELECT     VirtualMachines.DisplayName, VirtualMachines.OperatingSystem, VirtualMachines.PowerState, Nodes.Caption
          FROM         VirtualMachines INNER JOIN
                                Nodes ON VirtualMachines.HostNodeID = Nodes.NodeID

            • Re: Virtual machines queries
              contracer

              Please help me how put

              select (count) 

              and

              where (nodes.status='1')

              in this query.

              (I'm not a sql expert).

              Thanks

                • Re: Virtual machines queries
                  netlogix

                  You can try one of these:

                  SELECT     VirtualMachines.PowerState, COUNT(*)
                  FROM         VirtualMachines
                                        INNER JOIN Nodes as Host ON VirtualMachines.HostNodeID = Host.NodeID
                  Group by   VirtualMachines.PowerState


                  SELECT     Node.Status, COUNT(*)
                  FROM         VirtualMachines
                                        INNER JOIN Nodes as Host ON VirtualMachines.HostNodeID = Host.NodeID
                                        INNER JOIN Nodes as Node ON VirtualMachines.NodeID = Node.NodeID
                  Group by Node.Status

                  The first will show all machines with their current power status in ESX/vCenter and the second will only show you guests that are linked up to a node in orion.  This one will show the nodes that are not added.

                  Select VirtualMachines.DisplayName, VirtualMachines.PowerState
                  FROM         VirtualMachines
                                        INNER JOIN Nodes as Host ON VirtualMachines.HostNodeID = Host.NodeID
                  where  Not VirtualMachines.DisplayName in (select VirtualMachines.DisplayName
                                                          FROM         VirtualMachines
                                                             INNER JOIN Nodes as Host ON VirtualMachines.HostNodeID = Host.NodeID
                                                             INNER JOIN Nodes as Node ON VirtualMachines.NodeID = Node.NodeID
                                                        )