Hey patriot - long time no chat!
The way I handle this is by creating a floating point node custom property with the GMT offset for each node (e.g., -7 for PDT, -5 for CDT, and -4 for EDT). Then when I create the report, I limit the rows (in SWQL or SQL) to those where the hour is >= (dateTimeField + customPropertyUTCOffsetField) if the dateTimeField is UTC already (as it is with availability). Boom, you have a timezone aware report! If you want a hand with the particulars, give me a shout and we can hop on a GoToMeeting and chat about it.
can you check with the below query, if the query is getting executed directly on the database, i am able to get the output of the query, whereas if running on the application, the data is not getting fetched. :
SELECT TOP 10
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
Nodes.Caption AS NodeName,
Interfaces.InterfaceName AS Interface_Name,
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization,
Nodes.Region AS Region,
InterfaceTraffic.DateTime AS DateTime,
Interfaces.InterfaceID AS InterfaceID
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)
( DateTime BETWEEN 43219 AND 43249.9999884259 )
(Nodes.Region LIKE '%Networks%') AND
(DatePart(Hour,DateTime) >= 9) AND
(DatePart(Hour,DateTime) <= 18) AND
(Nodes.Caption = 'reckben-ingnpip01-2953349') AND
(DATEPART(weekday, DateTime) <> 1) AND
(DATEPART(weekday, DateTime) <> 7)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Caption, Interfaces.InterfaceName, Nodes.Region, InterfaceTraffic.DateTime, Interfaces.InterfaceID
ORDER BY SummaryMonth ASC
When you say it is not getting fetched, is there an error message, does the report time out?
The application goes in hung state.
Try something like setting the select to select top 10 and see if that executes, if so thats uaually a good indicator the report is taking too long to generate (default time out is 5 minutes, but it will show an error on the screen if it exceeds that normally)