List 30 guests on a VMWare host

Version 1

    This will display a total of 30 guests on a specified VMWare host.  Since the Custom MIB Poller doesn't support tables, I manually list 30.  You can easily edit the file to add or remove entries.


    This information can be included in an alert for a VMWare guest (if it is not a VM guest, it will be listed as 'Not a VM guest').  Here is the SQL query with label:


    VMWare Host  : ${SQL: SELECT CASE Nodes.Model when 'Virtual' THEN (SELECT Nodes.Caption FROM CustomPollerStatus, CustomPollerAssignment, Nodes WHERE (CustomPollerStatus.Status = '${NodeName}' AND (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (Nodes.NodeID = CustomPollerAssignment.NodeID))) ELSE 'Not a VM guest' END FROM Nodes WHERE NodeID = '${NodeID}'}


    This will work if you have the custom property Model created and populated with Virtual.  If you want to use it for known virtual guests, use this:


    VMWare Host  : ${SQL: SELECT Nodes.Caption FROM CustomPollerStatus, CustomPollerAssignment, Nodes WHERE (CustomPollerStatus.Status = '${NodeName}' AND (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (Nodes.NodeID = CustomPollerAssignment.NodeID))}


     It can be included in an information block, such as this (I use the entire SQL query for all variables so I can cut and paste the same information block across all alerts, i.e. Nodes, Applications, Interfaces, Custom MIB Pollers):


    --------------------------------------------------------------------------------------------------------------------------------------- 


    Other information about this server:


    Cabinet      : ${SQL: select Rack from Nodes where NodeID = ${NodeID}} ${SQL: select Rack_U_Location from Nodes where NodeID = ${NodeID}}
    Location     : ${SQL: select Location from Nodes where NodeID = ${NodeID}}
    VMWare Host  : ${SQL: SELECT Nodes.Caption FROM CustomPollerStatus, CustomPollerAssignment, Nodes WHERE (CustomPollerStatus.Status = '${NodeName}' AND (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (Nodes.NodeID = CustomPollerAssignment.NodeID))}
    Serial       : ${SQL: select SerialNumber from Nodes where NodeID = ${NodeID}}
    IP Address   : ${SQL: select IP_Address from Nodes where NodeID = ${NodeID}}
    Contact      : ${SQL: select Contact from Nodes where NodeID = ${NodeID}}
    Model        : ${SQL: select Model from Nodes where NodeID = ${NodeID}}
    Warranty     : ${SQL: select WarrantyExpiry from Nodes where NodeID = ${NodeID}}
    SystemState  : ${SQL: select SystemState from Nodes where NodeID = ${NodeID}}
    LastBoot     : ${SQL: select LastBoot from Nodes where NodeID = ${NodeID}}
    Logon Domain : ${SQL: select CustomPollerStatus.Status from Nodes, CustomPollerAssignment, CustomPollerStatus WHERE (Nodes.NodeID = ${NodeID} AND (Nodes.NodeID = CustomPollerAssignment.NodeID) AND (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.AssignmentName LIKE '%domPrimaryDomain%'))}
    Function     : ${SQL: select AppName from Nodes where NodeID = ${NodeID}}
    Maintenance
      Windows    : ${SQL: select MaintWindows from Nodes where NodeID = ${NodeID}}
    Patching
      Schedule   : ${SQL: select PatchSchedule from Nodes where NodeID = ${NodeID}}
    ---------------------------------------------------------------------------------------------------------------------------------------


     


    Here is a SQL query that can be used for the report section:


    SELECT Nodes.NodeID, Nodes.Caption AS VMWare_Host, CustomPollerStatus.Status AS VMWare_Guest
    FROM CustomPollerStatus,CustomPollerAssignment, Nodes
    WHERE (CustomPollerStatus.Status <> ''
           AND
                 (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID)
           AND
                    (Nodes.NodeID = CustomPollerAssignment.NodeID)
            AND
                   (CustomPollerAssignment.AssignmentName LIKE '%vmDisplayName%'))
    ORDER BY VMWare_Host, VMWare_Guest


    --------------------------------------------------------------------------------------------------------------------------------------- 


    If you want to list the current VMWare guests on the VMHost in an alert, add code such as this:


    List of VMWare guests currently on this server:
    -----------------------------------------------


    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-00%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-01%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-02%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-03%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-04%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-05%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-06%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-07%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-08%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-09%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-10%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-11%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-12%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-13%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-14%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-15%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-16%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-17%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-18%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-19%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-20%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-21%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-22%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-23%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-24%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-25%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-26%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-27%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-28%')}
    ${SQL: SELECT TOP 1 CustomPollerStatus.Status FROM CustomPollerStatus,CustomPollerAssignment, Nodes WHERE  (CustomPollerStatus.CustomPollerAssignmentID =  CustomPollerAssignment.CustomPollerAssignmentID) AND (CustomPollerAssignment.NodeID = '${NodeID}') AND(CustomPollerAssignment.AssignmentName LIKE 'vmDisplayName-29%')}


    ---------------------------------------------------------------------------------------------------------------------------------------