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.000	B	B	14	65
2015-12-01 00:00:00.000	Rus	Rus	13	65
2015-12-01 00:00:00.000	S	S	36	38
2015-12-01 00:00:00.000	S	S	10	15
2015-12-01 00:00:00.000	Ti	Rus	5	15
2015-12-01 00:00:00.000	Ti	T	16	35
2015-12-01 00:00:00.000	Ti	Rus	3	29
2015-12-01 00:00:00.000	Ti	T	6	29
2015-12-01 00:00:00.000	Ti	T	26	48

Any help would be greatly appreciated.

Kind regards,

Z

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.