2 Replies Latest reply on Dec 16, 2015 2:44 AM by zyontist

    Business hours report on Virtual Clusters

    zyontist

      Hi, I am trying to run a monthly report (previous month) on CPU and Memory load but only for Mon-Fri and Business hours of 08:00-18:00

       

      I have seen some ways of doing that on Thwack but I can't get it to work so i guess I am not using the correct tables or information as I can't get it for the previous month.

       

      SELECT CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101) AS SummaryMonth, VIM_Clusters.Name AS ClusterName, VIM_DataCenters.Name AS VCName,

      ROUND(AVG(CPULoad),0) as CPULoad, ROUND(AVG(MemoryUsage),0) AS MemLoad

      FROM

      (VIM_Clusters JOIN VIM_DataCenters ON (VIM_Clusters.DataCenterID = VIM_DataCenters.DataCenterID)),

       

      (select VIM_ClusterStatistics_Baseline.ClusterID, DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), [Timestamp]) AS [DateTime]

      from VIM_ClusterStatistics_Baseline JOIN VIM_Clusters ON (VIM_ClusterStatistics_Baseline.ClusterID = VIM_Clusters.ClusterID) ) t1

       

      WHERE

        (VIM_Clusters.Name LIKE 'Ti%' OR

        VIM_Clusters.Name LIKE 'S%' OR

        VIM_Clusters.Name LIKE 'B%' OR

        VIM_Clusters.Name LIKE 'Rus%'

        AND (DATEPART(weekday, t1.[DateTime]) >= 2)

        AND (DATEPART(weekday, t1.[DateTime]) <= 6)

        AND (Convert(Char,t1.[DateTime],108) >= '08:00')

        AND (Convert(Char,t1.[DateTime],108) <= '18:00'))

       

      group by CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101), VIM_Clusters.Name, VIM_DataCenters.Name

       

       

      VIM_ClusterStatistics_Baseline was the only Virtual Cluster table I could find TimeStamp in? This displays the data for December but I wanted it for November? Am I using the correct variables for CPU Load and Memory Load as well as the correct tables?

       

      I get the name of the DCs and VC's in the correct order so I know that bit is OK I just think it's something to do with the TimeStamp?

       

      Output from script:

       

      Summary Month               VC  DC CPU Mem  

      2015-12-01 00:00:00.000BB14

      65

      2015-12-01 00:00:00.000RusRus1365
      2015-12-01 00:00:00.000SS3638
      2015-12-01 00:00:00.000SS1015
      2015-12-01 00:00:00.000TiRus515
      2015-12-01 00:00:00.000TiT1635
      2015-12-01 00:00:00.000TiRus329
      2015-12-01 00:00:00.000TiT629
      2015-12-01 00:00:00.000TiT2648

       

      Any help would be greatly appreciated.

       

      Kind regards,

       

      Z

        • Re: Business hours report on Virtual Clusters
          silverbacksays

          One thing you could do, to see if using the VIM tables is causing the problem, would be to assign a new custom property to your nodes for 'Cluster', and put the relevant information in each one. Then, you could use the main tables and timestamp fields, rather than being restricted to the ones in the VIM section.

           

          Also, be mindful of the stats roll-ups, as hourly stats will be rolled up into the daily stats tables after 29 days by default (IIRC), if you don't run the report on the evening of the 29th day, you'll loose some data for longer months. Not a lot of data, but it might skew things a little.

            • Re: Business hours report on Virtual Clusters
              zyontist

              Silverbacksays,

               

              Hi, thanks for the response - as I am not an SQL and really tripping around in the dark here where would I find the tables you mention with the timestamp fields? I have tried other date methods and found one that works but from a month previous to the day it is run:

              DateAdd(month, -1, Convert(date, GetDate())) so this is getting closer.

               

              Kind regards,

               

              Z.