Hi, I have this SWQL query but I want it to show historical data. There are options to choose "peaks BPS" from a time period but I cannot see the option to show "peak percent utilisation" from a given time period.
This is a pretty simple and essential requirement for trend analysis. Please can you advise on how to achieve this?
The goal is to be able to compare peak utilisation from one month with the peak utilisation from the current month.
Here is what I have:
SELECT [data].[DisplayName] AS [DisplayName],[data].[Node].[DisplayName] AS [DisplayName1],[data].[InstanceSiteId] AS [InstanceSiteId]
FROM orion.npm.interfaces AS data
WHERE
(((([data].[FullName]) LIKE '%'+('hrt') + '%') OR (([data].[FullName]) LIKE '%'+('srt') + '%')) AND ((([data].[OutPercentUtil]) >= (80)) OR (([data].[InPercentUtil]) >= (80))))
WITH NOLOCK
What I would like (but it says invalid query)
SELECT
Interfaces.DisplayName AS InterfaceName,
Nodes.Caption AS NodeName,
MAX(HistoricalData.In_Maxbps / Interfaces.InterfaceSpeed * 100) AS PeakReceivedUtilization,
MAX(HistoricalData.Out_Maxbps / Interfaces.InterfaceSpeed * 100) AS PeakTransmitUtilization
FROM
Orion.NPM.Interfaces AS Interfaces
INNER JOIN
Orion.Nodes AS Nodes ON Interfaces.NodeID = Nodes.NodeID
INNER JOIN
Orion.NPM.InterfaceTraffic AS HistoricalData ON Interfaces.InterfaceID = HistoricalData.InterfaceID
WHERE
HistoricalData.DateTime >= DATEADD(MONTH, -1, GETUTCDATE())
AND
(Nodes.Caption LIKE '%srt%' OR Nodes.Caption LIKE '%hrt%')
GROUP BY
Interfaces.DisplayName, Nodes.Caption, Interfaces.InterfaceSpeed
HAVING
MAX(HistoricalData.In_Maxbps / Interfaces.InterfaceSpeed * 100) >= 80
OR MAX(HistoricalData.Out_Maxbps / Interfaces.InterfaceSpeed * 100) >= 80