CPU Load count matrix (sql) - with color

A good way to look at the CPU Load for "spikiness" is my making a simple heat map of the utilization - to count the number of times the CPULoad hits 0-10%, 10-20%, etc. over a period of time.

pastedImage_10.png

DECLARE @StartDate Datetime

DECLARE @EndDate Datetime

-- Last month

--SET @StartDate=DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)

--SET @EndDate=DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))

--Last n Weeks

--SET @StartDate=dateadd(dd, @weekDayStart-2, dateadd(wk, datediff(wk, 0, getdate()) - @numWeeks, 0))

--SET @EndDate=dateadd(ss, -1, dateadd(wk, datediff(wk, 0, getdate()), 0)-1)

-- Last Week

SET @StartDate=dateadd(dd, -1, dateadd(wk, datediff(wk, 0, getdate()) - 1, 0))

SET @EndDate=dateadd(ss, -1, dateadd(wk, datediff(wk, 0, getdate()), 0)-1)

--Current week

--SET @StartDate=dateadd(wk,0,dateadd(wk,datediff(wk,0,getdate()),0)-1)

--SET @EndDate=dateadd(dd,0,getdate())

SELECT

NodeCaption,

SUM(CASE WHEN utilization >= 0 and utilization <= 10 THEN total ELSE 0 END) AS "0-10",

SUM(CASE WHEN utilization > 10 and utilization <=20 THEN total ELSE 0 END)  AS "10-20",

SUM(CASE WHEN utilization > 20 and utilization <=30 THEN total ELSE 0 END)  AS "20-30",

SUM(CASE WHEN utilization > 30 and utilization <=40 THEN total ELSE 0 END)  AS "30-40",

SUM(CASE WHEN utilization > 40 and utilization <=50 THEN total ELSE 0 END)  AS "40-50",

SUM(CASE WHEN utilization > 50 and utilization <=60 THEN total ELSE 0 END)  AS "50-60",

SUM(CASE WHEN utilization > 60 and utilization <=70 THEN total ELSE 0 END)  AS "60-70",

SUM(CASE WHEN utilization > 70 and utilization <=80 THEN total ELSE 0 END)  AS "70-80",

SUM(CASE WHEN utilization > 80 and utilization <=90 THEN total ELSE 0 END)  AS "80-90",

SUM(CASE WHEN utilization > 90 and utilization <=100 THEN total ELSE 0 END) AS "90-100"

From

(SELECT

   c.datetime, n.nodeid as nodeid,   n.Caption as NodeCaption,

   Round(avg(avgload),2) AS utilization,  count(n.nodeid) as Total

FROM CPULoad c

   JOIN Nodes n on c.nodeid=n.nodeid

WHERE  datetime between @StartDate and @EndDate

GROUP by n.nodeid, n.caption, c.DateTime

) util

GROUP by util.nodecaption

order by 2 asc

Good information, but lets normalize it so it is easier to interpret the data.  By that, we'll change the count to a percentage.

pastedImage_0.png

DECLARE @StartDate Datetime

DECLARE @EndDate Datetime

-- Last month

--SET @StartDate=DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)

--SET @EndDate=DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))

--Last n Weeks

--SET @StartDate=dateadd(dd, @weekDayStart-2, dateadd(wk, datediff(wk, 0, getdate()) - @numWeeks, 0))

--SET @EndDate=dateadd(ss, -1, dateadd(wk, datediff(wk, 0, getdate()), 0)-1)

-- Last Week

SET @StartDate=dateadd(dd, -1, dateadd(wk, datediff(wk, 0, getdate()) - 1, 0))

SET @EndDate=dateadd(ss, -1, dateadd(wk, datediff(wk, 0, getdate()), 0)-1)

--Current week

--SET @StartDate=dateadd(wk,0,dateadd(wk,datediff(wk,0,getdate()),0)-1)

--SET @EndDate=dateadd(dd,0,getdate())

SELECT

NodeCaption,

round(cast(SUM(CASE WHEN utilization >= 0 and utilization <= 10 THEN total ELSE 0 END) as float)*100/sum(total),0) AS "0-10",

round(cast(SUM(CASE WHEN utilization > 10 and utilization <=20 THEN total ELSE 0 END)  as float)*100/sum(total),0) AS "10-20",

round(cast(SUM(CASE WHEN utilization > 20 and utilization <=30 THEN total ELSE 0 END)  as float)*100/sum(total),0) AS "20-30",

round(cast(SUM(CASE WHEN utilization > 30 and utilization <=40 THEN total ELSE 0 END)  as float)*100/sum(total),0) AS "30-40",

round(cast(SUM(CASE WHEN utilization > 40 and utilization <=50 THEN total ELSE 0 END)  as float)*100/sum(total),0) AS "40-50",

round(cast(SUM(CASE WHEN utilization > 50 and utilization <=60 THEN total ELSE 0 END)  as float)*100/sum(total),0) AS "50-60",

round(cast(SUM(CASE WHEN utilization > 60 and utilization <=70 THEN total ELSE 0 END)  as float)*100/sum(total),0) AS "60-70",

round(cast(SUM(CASE WHEN utilization > 70 and utilization <=80 THEN total ELSE 0 END)  as float)*100/sum(total),0) AS "70-80",

round(cast(SUM(CASE WHEN utilization > 80 and utilization <=90 THEN total ELSE 0 END)   as float)*100/sum(total),0) AS "80-90",

round(cast(SUM(CASE WHEN utilization > 90 and utilization <=100 THEN total ELSE 0 END)  as float)*100/sum(total),0) AS "90-100"

From

(SELECT

   c.datetime, n.nodeid as nodeid,   n.Caption as NodeCaption,

   Round(avg(avgload),2) AS utilization,  count(n.nodeid) as Total

FROM CPULoad c

   JOIN Nodes n on c.nodeid=n.nodeid

WHERE  datetime between @StartDate and @EndDate

GROUP by n.nodeid, n.caption, c.DateTime

) util

GROUP by util.nodecaption

order by 2 asc

Good.  But it would be awesome with color.   Please the color files in the appropriate location.   (0.png, 1.png...10.png)

pastedImage_0.png

DECLARE @StartDate Datetime

DECLARE @EndDate Datetime

-- Last month

--SET @StartDate=DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)

--SET @EndDate=DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))

--Last n Weeks

--SET @StartDate=dateadd(dd, @weekDayStart-2, dateadd(wk, datediff(wk, 0, getdate()) - @numWeeks, 0))

--SET @EndDate=dateadd(ss, -1, dateadd(wk, datediff(wk, 0, getdate()), 0)-1)

-- Last Week

SET @StartDate=dateadd(dd, -1, dateadd(wk, datediff(wk, 0, getdate()) - 1, 0))

SET @EndDate=dateadd(ss, -1, dateadd(wk, datediff(wk, 0, getdate()), 0)-1)

--Current week

--SET @StartDate=dateadd(wk,0,dateadd(wk,datediff(wk,0,getdate()),0)-1)

--SET @EndDate=dateadd(dd,0,getdate())

SELECT

'<img src="/orion/images/statusicons/Small-'+util.StatusLED+'"/>' +

'<a href="thwack.solarwinds.com/.../View.aspx as nvarchar)+'">'+util.NodeCaption+'</>' as [Node]

,'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization >= 0 and utilization <= 10 THEN total ELSE 0 END) as float)*10/sum(total),0) as varchar(2))+'.png"></>' AS "0-10",

'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization > 10 and utilization <=20 THEN total ELSE 0 END)  as float)*10/sum(total),0)  as varchar(2))+'.png"></>' AS "10-20",

'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization > 20 and utilization <=30 THEN total ELSE 0 END)  as float)*10/sum(total),0)  as varchar(2))+'.png"></>' AS "20-30",

'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization > 30 and utilization <=40 THEN total ELSE 0 END)  as float)*10/sum(total),0)  as varchar(2))+'.png"></>' AS "30-40",

'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization > 40 and utilization <=50 THEN total ELSE 0 END)  as float)*10/sum(total),0)  as varchar(2))+'.png"></>' AS "40-50",

'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization > 50 and utilization <=60 THEN total ELSE 0 END)  as float)*10/sum(total),0)  as varchar(2))+'.png"></>' AS "50-60",

'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization > 60 and utilization <=70 THEN total ELSE 0 END)  as float)*10/sum(total),0)  as varchar(2))+'.png"></>' AS "60-70",

'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization > 70 and utilization <=80 THEN total ELSE 0 END)  as float)*10/sum(total),0)  as varchar(2))+'.png"></>' AS "70-80",

'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization > 80 and utilization <=90 THEN total ELSE 0 END)   as float)*10/sum(total),0)  as varchar(2))+'.png"></>' AS "80-90",

'<img src="/orion/images/statusicons/'+cast(round(cast(SUM(CASE WHEN utilization > 90 and utilization <=100 THEN total ELSE 0 END)  as float)*10/sum(total),0)  as varchar(2))+'.png"></>' AS "90-100"

From

(SELECT

   c.datetime, n.nodeid as nodeid,   n.Caption as NodeCaption, n.StatusLED,

   Round(avg(avgload),2) AS utilization,  count(n.nodeid) as Total

FROM CPULoad c

   JOIN Nodes n on c.nodeid=n.nodeid

WHERE  datetime between @StartDate and @EndDate

GROUP by n.nodeid, n.caption, c.DateTime, n.StatusLED

) util

GROUP by util.nodecaption, util.nodeid, util.StatusLED

order by 2 asc