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%')}
---------------------------------------------------------------------------------------------------------------------------------------