0 Replies Latest reply on Sep 6, 2018 12:52 PM by storn

    APM and Virtual Machine SQL Report...

    storn

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