Hello,
I have the following report query from Solarwinds and would like to replicate it within Power BI.
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_In95,
Maxbps_Out95,
Maxbps_95 FROM Orion.Nodes INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID INNER JOIN ( SELECT OuterInterfaceTraffic.InterfaceID,
(SELECT MAX(InAveragebps) as maxInMaxbps FROM ( SELECT TOP 95 PERCENT InAveragebps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', 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 BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE()) ORDER BY OutMaxbps ASC )) AS Maxbps_Out95,
(SELECT MAX(Maxbps) as maxMaxbps FROM ( SELECT TOP 95 PERCENT Maxbps FROM (SELECT (CASE WHEN OutAveragebps > InAveragebps THEN OutAveragebps ELSE InAveragebps END) AS Maxbps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())) AS MaxbpsSet ORDER BY Maxbps ASC )) AS Maxbps_95 FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic WHERE OuterInterfaceTraffic.DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE()) GROUP BY OuterInterfaceTraffic.InterfaceID ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID
The problem is that DateTrunc is not a recognized built-in function name. Can anyone please help me customize this query to work within Power BI?
