0 Replies Latest reply on Mar 21, 2017 7:37 AM by mprobus

    Report on CPU Over Multiple Data Polls

    mprobus

      I have the below query that I have been using to get cpu and memory utilization.  However, I recently had a request to change it add in a field for both cpu and memory that shows a sustained high usage.  So, what I need is to show the max value of the highest average over multiple data pulls or over a period of time.  What I'm thinking is to average out the data in 20 minutes averages or two consecutive data pulls whichever is easier, then give the max of this.  So, if I had 4 data values, it would need to be to be able to compare 1 to 2, 2 to 3, 3 to 4.  Any ideas?

       

      SELECT     

          Caption,

          count_cpuIndex AS CPUCount,

          max_maxLoad AS MaxCPU,

          avg_avgLoad AS AvgCPU,

          ROUND((TotalMemory / 1073741824),0) AS MemCount,

          ROUND(max_maxMemoryUsedPct * 100, 2) AS MaxMem,

          ROUND(avg_avgPercentMemoryUsed, 2) AS AvgMem

      FROM

         (SELECT

              caption,

              nodeid

          FROM

              Nodes) n INNER JOIN

         (SELECT

              nodeid,

              COUNT(DISTINCT CPUIndex) AS count_cpuIndex

         FROM

              CPUMultiLoad_Detail

        GROUP BY

          nodeid) d ON

         n.NodeID = d.NodeID INNER JOIN

         (SELECT

             nodeid,

             TotalMemory,

             MAX(MaxLoad) AS max_maxLoad,

             AVG(AvgLoad) AS avg_avgLoad,

             MAX(MaxMemoryUsed / TotalMemory ) AS max_maxMemoryUsedPct,

             AVG(AvgPercentMemoryUsed) AS avg_avgPercentMemoryUsed

         FROM

              CPULoad

         WHERE

              Datetime >= DATEADD(MONTH,datediff(MONTH,0,getdate())-1,0)  AND

              Datetime < DATEADD(MONTH,datediff(MONTH,0,getdate()),0)

         GROUP BY

          nodeid,

          TotalMemory) l ON

         n.NodeID = l.NodeID