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.
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.
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)
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