Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 10

APM and Virtual Machine SQL Report...

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


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....

0 Kudos
0 Replies