dbo.GetInBps95th SQL Broken

jajao555

We have the query below that seems to have stopped working. I haven't been able to find a new table in the Stored Procedures that seems like it might be similar. Is there a SWQL equivalent we should use instead?

These three seem to not be reconized in SQL and the query doesn't work. 

dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95

SET NOCOUNT OFF
SET ROWCOUNT 0

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = ${FromTime}
SET @EndDate = ${ToTime}

SELECT	
		Interfaces.InterfaceReportAgency,
		Interfaces.InterfaceReport,
		Interfaces.InterfaceId,
		Interfaces.InBandwidth,
		Interfaces.OutBandwidth,
		NodesOLD.NodeID,
		NodesOLD.Caption AS NodeName,
		(Interfaces.InterfaceSpeed/1000000) AS ProvisionedBandwidthInMb,
		Interfaces.Caption AS Interface_Caption,
		Interfaces.InterfaceIcon AS Interface_Icon,
		Maxbps_In95,
		Maxbps_Out95,
		Maxbps_95
FROM [NetPerfMon].[dbo].[NodesOLD]
INNER JOIN [NetPerfMon].[dbo].[Interfaces] ON NodesOLD.NodeID = Interfaces.NodeID
INNER JOIN (
	SELECT	InterfaceID,
			dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
			dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
			dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
	FROM [NetPerfMon].[dbo].[InterfaceTraffic]
	WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate
	GROUP BY InterfaceID
) TrafficStat
ON Interfaces.InterfaceID = TrafficStat.InterfaceID

WHERE Interfaces.InterfaceReport = 'Internet' OR Interfaces.InterfaceReport = 'DataCenter'
ORDER BY Interfaces.InterfaceReportAgency</pre>Also SQL shows an error for these two lines under the first curly bracket. Was there also a change for how those are handled?SET @StartDate = ${FromTime}
SET @EndDate = ${ToTime}

KMSigma.SWI

That's an OOTB Report that's been converted to use SWQL (not SQL) to do the same work.

You can edit the report (on your system) and see the datasource;

SELECT Interfaces.InterfaceId
     , Nodes.NodeID
     , Nodes.Caption AS NodeName
     , Nodes.DetailsUrl AS NDetailsUrl
     , Nodes.VendorIcon AS Vendor
     , Interfaces.Caption AS Interface_Caption
     , Interfaces.InterfaceIcon AS Interface_Icon
     , Interfaces.DetailsUrl AS IDetailsUrl
     , Maxbps_In90
     , Maxbps_Out90
     , Maxbps_In95
     , Maxbps_Out95
     , Maxbps_In99
     , Maxbps_Out99
FROM Orion.Nodes
INNER JOIN Orion.NPM.Interfaces
     ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
     SELECT OuterInterfaceTraffic.InterfaceID
          , (
               SELECT MAX(InAverageBps) AS maxInAverageBps
               FROM (
                    SELECT TOP 90 PERCENT InAveragebps
                    FROM Orion.NPM.InterfaceTraffic
                    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
                         AND DATETIME >= AddDay(- 7, GETUTCDATE())
                    ORDER BY InAveragebps ASC
                    )
               ) AS Maxbps_In90
          , (
               SELECT MAX(OutAverageBps) AS maxOutAverageBps
               FROM (
                    SELECT TOP 90 PERCENT OutAveragebps
                    FROM Orion.NPM.InterfaceTraffic
                    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
                         AND DATETIME >= AddDay(- 7, GETUTCDATE())
                    ORDER BY OutAveragebps ASC
                    )
               ) AS Maxbps_Out90
          , (
               SELECT MAX(InAveragebps) AS maxInMaxbps
               FROM (
                    SELECT TOP 95 PERCENT InAveragebps
                    FROM Orion.NPM.InterfaceTraffic
                    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
                         AND DATETIME >= AddDay(- 7, GETUTCDATE())
                    ORDER BY InMaxbps ASC
                    )
               ) AS Maxbps_In95
          , (
               SELECT MAX(OutAveragebps) AS maxOutMaxbps
               FROM (
                    SELECT TOP 95 PERCENT OutAveragebps
                    FROM Orion.NPM.InterfaceTraffic
                    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
                         AND DATETIME >= AddDay(- 7, GETUTCDATE())
                    ORDER BY OutMaxbps ASC
                    )
               ) AS Maxbps_Out95
          , (
               SELECT MAX(InAverageBps) AS maxInAverageBps
               FROM (
                    SELECT TOP 99 PERCENT InAveragebps
                    FROM Orion.NPM.InterfaceTraffic
                    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
                         AND DATETIME >= AddDay(- 7, GETUTCDATE())
                    ORDER BY InAveragebps ASC
                    )
               ) AS Maxbps_In99
          , (
               SELECT MAX(OutAverageBps) AS maxOutAverageBps
               FROM (
                    SELECT TOP 99 PERCENT OutAveragebps
                    FROM Orion.NPM.InterfaceTraffic
                    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
                         AND DATETIME >= AddDay(- 7, GETUTCDATE())
                    ORDER BY OutAveragebps ASC
                    )
               ) AS Maxbps_Out99
     FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic
     WHERE OuterInterfaceTraffic.DATETIME >= AddDay(- 7, GETUTCDATE())
     GROUP BY OuterInterfaceTraffic.InterfaceID
     ) TrafficStat
     ON Interfaces.InterfaceID = TrafficStat.InterfaceID

Be aware that this is not a light query.  On my lab system (small device count, but also underpowered), it returned 145 rows, but took 58.31 seconds.