Hi all!
Hoping for a shove in the right direction as we're struggling to resolve this issue.
We just updated our instance of Orion (installed NPM version 2020.2.5) and are having some issues with a custom report that was migrated from the old to the new.
This report was a custom SQL report (SQL language below) that selects that selects all nodes of a certain vendor type, averages their availability for that day, then reports on that for each day.
This report ran as scheduled every business day and pulled all data required... until today. Today, it pulled the exact same data that it did yesterday (it pulled the average availability for 9/13 and prior). We would have expected it to include a line for yesterday (9/14).
No changes were made to the SQL language or the report at all, and I'm completely stumped. SQL Language reproduced below (it's not ideal or even clean, but until today... it worked).
Custom SQL query used in the report:
select Vendor, avg(average_of_availability) as [Availability], SummaryDate as [Date] from (SELECT TOP 10000
CONVERT(DateTime,Floor(Cast(Cast(DateTime AS datetime) AS Float)),0) AS SummaryDate,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
Nodes.Caption AS NodeName,
Nodes.SysName AS System_Name,
Nodes.Vendor AS Vendor,
Nodes.VendorIcon AS Vendor_Icon
FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
( DateTime between '1/1/2017 00:01 AM' and (DATEADD(dd, DATEDIFF(dd,0, getdate()) , 0)))
AND
(
(DatePart(Hour,DateTime) >= 7) AND
(DatePart(Hour,DateTime) <= 19) AND
(DatePart(Weekday,DateTime) <> 1) AND
(DatePart(Weekday,DateTime) <> 7) AND
(
(Nodes.Vendor = 'Meraki Networks, Inc.')
)
)
GROUP BY CONVERT(DateTime,Floor(Cast(Cast(DateTime AS datetime) AS Float)),0),
Nodes.Caption, Nodes.SysName, Nodes.Vendor, Nodes.VendorIcon
)as dataset
group by vendor, SummaryDate
