I'm trying to write a query that will tell me the maximum Receive Utilization ever reach on a given interface over the last 30 days along side the current Receive Utilization. I got this far using data modeler in the modern dashboard, but I don't think its correct. Is there a better, more accurate way?
SELECT n.FullName,n.detailsurl,n.InPercentUtil,Round(aggSub1._max, 0) AS Orion_NPM_InterfaceTraffic_InPercentUtil_max FROM Orion.NPM.Interfaces n LEFT JOIN ( SELECT i.InterfaceID,i.InstanceSiteId , max(i.InPercentUtil) AS _max FROM Orion.NPM.InterfaceTraffic i WHERE (i.DateTime BETWEEN '2020-10-24T18:26:47Z' AND '2020-11-23T19:26:47Z') GROUP BY i.InterfaceID,i.InstanceSiteId ) aggSub1 ON aggSub1.InterfaceID=n.InterfaceID AND aggSub1.InstanceSiteId=n.InstanceSiteId WHERE (FullName LIKE '%#inet%') ORDER BY Orion_NPM_InterfaceTraffic_InPercentUtil_max desc