Interface Utilisation and Availability

SW_United

So I'm trying to write a SWQL query that will display last months WAN Interface In and Out through put, show the interface Rx/Tx speeds, and filter custom property on the Node "NodeWANLink" to equal Yes.

I've got this far with the query but it shows zero results.  

SELECT
I.FullName,
DateTrunc('Hour', NIT.DateTime) AS Date,
ROUND(AVG(nit.InAveragebps)/1000000, 1) AS AVG_IN_MBPS,
ROUND(MIN(nit.InMinbps)/1000000, 2) AS MIN_IN_MBPS,
ROUND(MAX(nit.InMaxbps)/1000000, 2) AS MAX_IN_MBPS,
ROUND(AVG(nit.OutAveragebps)/1000000, 1) AS AVG_OUT_MBPS,
ROUND(MIN(nit.OutMinbps)/1000000, 2) AS MIN_OUT_MBPS,
ROUND(MAX(nit.OutMaxbps)/1000000, 2) AS MAX_OUT_MBPS
FROM Orion.NPM.InterfaceTraffic NIT
INNER JOIN Orion.NPM.Interfaces I ON I.InterfaceID = NIT.InterfaceID
INNER JOIN Orion.NodesCustomProperties CP ON CP.NodeID = i.NodeID
WHERE I.CustomProperties.WAN = 'Yes'
AND MONTH(ADDMONTH(-1,GETDATE())) = MONTH(nit.DateTime)
GROUP BY I.FullName, DateTrunc('Hour', NIT.DateTime)
ORDER BY ROUND(AVG(InAveragebps)/1000000, 1) DESC

UPDATE 07/07/2025 : I've changed the query to reflect this now = I reckon it should be changed over to interface custom Property of WAN instead is my 1st observation.

2nd I need to add last months Interface availability as onto each line and not sure how this is done.

Thanks muchly in advance thwackers 

UPDATED: @chad.every  Thanks for grabbing last months data query string, I've updated my query now.

chad.every

So this line, with the >= is going to give you a rolling 30-ish days. GetDate() grabs the date every time it is executed.

AND nit.datetime >= addmonth(-1,getutcDate())

What I've done with similar queries in the past is to update the WHERE to this:

AND MONTH(ADDMONTH(-1,GETDATE())) = MONTH(nit.DateTime) 
AND DAYDIFF( nit.DateTime , GETDATE()) < 61

• In the first line: I only look for the MONTH() value minus 1
• In the second line: I max the total time to look in the past to 61 days. e.g. if i ran the report on July 31st, 61 days brings me to the beginning of June. I also have this in there if for some reason I have a long retention period that spans more than 1 year/12 months.