dbo.GetInBps95th SQL Broken

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

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}

Top Replies

  • 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…

Parents
  • 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.

  • Nice thanks I'll look into that then.

Reply Children
No Data