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!
Trying to pull last months data and have it show me the dates
in SQL
Posted
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!