Interface(WAN-Bandwidth) Heatmap

I have used this method to visualize the bandwidth usage. Getting the numbers are useful, but to ingest large amounts of information - can't beat a graphics.

For this example, there is a interface custom property called isWan (yes/no) to indicate that this an interesting interface. Once we know what we want to look at, we need to define what time period. The time period is defined in the set command (last week, last month, etc).

Now it's a simple matter of tallying the number of time the bandwidth is within certain ranges (0-10%, 10%-20%, etc). Since we know the number of sample, now it's a matter of just normalizing the data to the range (1-10).

In order to display the graphics, there are 10 graphics which needs to be created and put inside /Orion/images/statusicons/1.gif, 2.gif, etc.

DECLARE @WeekDayStart INT

DECLARE @WeekDayEnd INT

DECLARE @TimeStart DATETIME

DECLARE @TimeEnd DATETIME

DECLARE @NumWeeks INT

DECLARE @StartDate Datetime

DECLARE @EndDate Datetime

SET @WeekDayStart = 1

SET @WeekDayEnd = 7

SET @TimeStart = '00:00'

SET @TimeEnd = '23:59'

SET @NumWeeks = 1

-- 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-'+nStatusLED+'"></>'+

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

,nCaption

,'<img src="/orion/images/statusicons/Small-'+iStatusLED+'"></>'+

'<a href="thwack.solarwinds.com/.../View.aspx as varchar)+'">'+iCaption+'</>' as [Interface]

,iCaption

,case when inbandwidth < 1000000000 then concat('In',inbandwidth/1000000,' Mbps')

when inbandwidth >= 1000000000 then concat('In',inbandwidth/1000000000,' Gbps')

else 'In'+str(inbandwidth)+'bps' end as [Bandwidth],

'<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 nvarchar)+'.png"></>' AS [1],

'<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 nvarchar)+'.png"></>' AS [2],

'<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 [3],

'<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 [4],

'<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 [5],

'<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 [6],

'<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 [7],

'<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 [8],

'<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 [9],

'<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 [A]

From

(SELECT

it.datetime, n.nodeid, n.Caption as nCaption, i.interfaceid, i.InterfaceName as iCaption, i.fullname as iFullname, i.InBandwidth as inbandwidth,

i.OutBandwidth as outbandwidth, n.statusled as [nStatusled], i.StatusLED as [iStatusled]

--Add custom properties here

,Round(avg(it.In_Averagebps/i.inbandwidth*100),2) AS utilization, count(it.interfaceid) as Total

FROM Interfaces i

JOIN InterfaceTraffic it on i.interfaceid=it.interfaceid

JOIN Nodes n on i.nodeid=n.nodeid

WHERE i.inbandwidth > 0 and (i.isWan=1) and

datetime between @StartDate and @EndDate and

((DATEPART(weekday, DateTime) >= @WeekDayStart) AND (DATEPART(weekday, DateTime) <= @WeekDayEnd) AND

(Convert(Char,DateTime,108) >= @TimeStart) AND (Convert(Char,DateTime,108) <= @TimeEnd))

GROUP by n.nodeid, n.caption,

--Add custom properties here

i.InterfaceID, i.FullName,it.DateTime, i.InBandwidth, i.OutBandwidth, i.caption, n.StatusLED, i.StatusLED, i.InterfaceName

) util

GROUP by util.nCaption, util.iCaption

--Add custom properties here

,util.inbandwidth, util.outbandwidth, util.nstatusled, util.nodeid, util.iStatusled, util.InterfaceID

union all

SELECT

'<img src="/orion/images/statusicons/Small-'+nStatusLED+'"></>'+

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

,ncaption

,'<img src="/orion/images/statusicons/Small-'+iStatusLED+'"></>'+

'<a href="thwack.solarwinds.com/.../View.aspx as varchar)+'">'+iCaption+'</>' as [Interface]

,iCaption

--Add custom properties here

,case when outbandwidth < 1000000000 then concat('Out',outbandwidth/1000000,' Mbps')

when outbandwidth >= 1000000000 then concat('Out',outbandwidth/1000000000,' Gbps')

else 'Out'+str(outbandwidth)+' bbps' end as [Bandwidth],