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

    Business hours report on Virtual Clusters


      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


      (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



        (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


      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,



        • Re: Business hours report on Virtual Clusters

          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



              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,