Hello Thwack Community.
I have written the SQL code below on the Orion Report Writer to generate the availability and utilisation statistics of interfaces for the last month. However I am having challenges creating an SQL code for generating the statistics for the last 6 months. Please see below and assist.Thank you in advance.
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0));
WITH T1 AS
(
SELECT
IA.NodeID,
IA.InterfaceID,
SUM(IA.Availability * IA.Weight) AS Sum1,
SUM(IA.Weight) AS Sum2
from InterfaceAvailability IA WITH(NOLOCK)
where
IA.DateTime between @StartDate and @EndDate
group by
IA.NodeID,
IA.InterfaceID
)
SELECT
IA.nodeid, IA.interfaceid, IA.NodesVendorIcon, IA.NodeName,
IA.InterfaceIcon, IA.InterfaceCaption,
IA.Availability
FROM (
SELECT
Nodes.NodeID,
Interfaces.InterfaceID,
SUM(Sum1) / SUM(Sum2) AS Availability,
Interfaces.InterfaceIcon, Interfaces.Caption as InterfaceCaption,
Nodes.Caption as NodeName,
Nodes.VendorIcon as NodesVendorIcon
FROM T1
INNER JOIN Nodes WITH(NOLOCK)
ON
T1.NodeID = Nodes.NodeID
INNER JOIN Interfaces WITH(NOLOCK)
ON
T1.InterfaceID = Interfaces.InterfaceID
GROUP BY
Nodes.NodeID, Interfaces.InterfaceID, Interfaces.InterfaceIcon, Interfaces.Caption, Nodes.Caption, Nodes.VendorIcon
)
AS IA
ORDER BY
NodeID, InterfaceID
