Attempting to create a custom SQL Report in Report write to include:
ESX Hostname, VM (Displayname), Application and component name (running on VM's) and CPU utilization AVG and Peak)
edited out key tags with ***
Attempting to join APM with virtual machine tables to include the ESX hostname, however, the hostname is returning the VM (Displayname)..
What am I missing, what do I need to do to include the ESX hostname in the report...
Here is the SQL
SELECT TOP 10000 Nodes.Caption AS HostName,VirtualMachines.DisplayName AS VM,
APM_AlertsAndReportsData.ApplicationName AS Application_Name,
APM_AlertsAndReportsData.ComponentName AS Component_Name,
MIN(APM_CPUAndMemoryUsage.MinPercentCPU) AS MIN_of_Minimum_Percent_CPU,
AVG(APM_CPUAndMemoryUsage.AvgPercentCPU) AS AVERAGE_of_Average_Percent_CPU,
MAX(APM_CPUAndMemoryUsage.MaxPercentCPU) AS MAX_of_Peak_Percent_CPU
FROM [dbo].[Nodes] [Nodes]
INNER JOIN [dbo].[APM_AlertsAndReportsData] [APM_AlertsAndReportsData] ON ([Nodes].[NodeID] = [APM_AlertsAndReportsData].[NodeId])
INNER JOIN [dbo].[APM_CPUAndMemoryUsage] [APM_CPUAndMemoryUsage] ON ([APM_AlertsAndReportsData].[ComponentID] = [APM_CPUAndMemoryUsage].[ComponentID])
INNER JOIN [dbo].[VirtualMachines] [VirtualMachines] ON ([Nodes].[NodeID] = [VirtualMachines].[NodeID])
WHERE
APM_CPUAndMemoryUsage.DateTime BETWEEN 43338 AND 43345.625
AND ((APM_AlertsAndReportsData.ApplicationName LIKE '%***%') OR (APM_AlertsAndReportsData.ApplicationName LIKE '%***%') OR (APM_AlertsAndReportsData.ApplicationName LIKE '%***%'))
GROUP BY Nodes.Caption,VirtualMachines.DisplayName, APM_AlertsAndReportsData.ApplicationName, APM_AlertsAndReportsData.ComponentName;
Any help is appreciated....
