This is the SQL code I currently have on Solarwinds:
SET NOCOUNT OFF
SET ROWCOUNT 0
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))
SELECT Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
Maxbps_In95,
Maxbps_Out95
FROM Nodes
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
SELECT InterfaceID,
dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
FROM InterfaceTraffic
WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate
GROUP BY InterfaceID
) TrafficStat
ON Interfaces.InterfaceID = TrafficStat.InterfaceID
AND
(
(Interfaces.Caption LIKE '%Eth%') AND
(Interfaces.Caption LIKE '%CUST:%')
)
ORDER BY NodeName, Interface_Caption
This code returns last months 95th percentile as one integer. I'm wondering how i can get it to return the data from each data from last month, and if at all possible every hour from every day of last month. Any help is appreciated!
